SQL Server 2012 Tutorials – Details

Here is the detailed information on our SQL Server 2012 Training. Please note that these videos are available under any of our product versions: Online download, DVD and Unlimited SQL Server Tutorials.

1. Installation of SQL Server 2012

In this training demo on SQL Server 2012, we start by going over the hardware and software requirements for SQL Server 2012 installation. This includes things like CPU, Operating System, RAM and Hard Disk information. In addition you also need .NET framework 3.5 and IE 7 0.1 before you can proceed further. Additional information is avail from location on Microsoft web site. For the purposes of this SQL Server 2012 tutorial, we are going to use the 3 files option for SQL Server 2012 installation. Next we run the Setup.exe which will launch the SQL Server Installation center. Before any software is installed, you have to make sure that Setup Support rules and files are installed.  Another way to think about it is Prerequisites for SQL installation. Next we are going to choose a set of options including stand-alone SQL 2012 installation, feature selection like database engine, SSIS, management tools, authentication choices, security role consideration, data and log file location. We highlight the best practices that are used by the IT industry and then continue the video tutorial with the Install process. At the end, a setup log file is generated which has all the information regarding the installation. This should be stored in a secure place for future reference. After SQL 2012 installation finishes, you can use the SQL Server 2012 Management Studio (SSMS) and verify that the installation process went smoothly.  In addition, you can check the MS SQL services using SQL 2012 Configuration Manager.

2. SQL Server 2012 Configuration Manager

In this video tutorial on MS SQL 2012, we are going to cover SQL 2012 configuration manager. It combines three utilities including Client Network utility, Server Network utility and Service Manager. Using the SQL Server configuration manager you can stop, start, pause and resume services in SQL Server 2012. You can also choose to run SQL services automatically or in a manual mode. Finally you can use custom properties to fine-tune these database services. Next we cover how to modify the Windows account under which SQL Server services should run as. Microsoft recommended best practice is to use a domain account as opposed to using a Local system account or a Network service. Next in the SQL Server 2012 training, we move on to configuring network protocols including Shared memory, TCP/IP and Named pipes for SQL Server 2012. We highlight these practices including changing the default port 1433 that SQL Server listens on. In order to avoid communication issues between the server and the client, you want to make sure that network protocol between the server and the native client is the same. Last item we mention is the alias that can be utilized instead of an IP address to connect to a SQL 2012 Database Server.

3. Creating database objects in SQL Server 2012

In this SQL 2012 training, we are going to show you how to create a database and a table in SQL Server 2012 version. We use two different ways to do this, SQL Server Management Studio and Transact SQL also known as TSQL. Using SSMS we create a School database and pay particular attention to the file locations and Auto growth parameters for MSSQL 2012. We move on and create HR database using SQL command and enter specific values for data and log file locations. Next we go ahead and create SQL 2012 Server tables which consist of columns and rows. The Database instructor discusses important considerations like data type, field size, primary key and foreign key constraints. For the School database, we create Teachers table using SSMS and then using a SQL Server TSQL script to create Students table. Both these tables contain personal information and will have fields like ID, name, contact and address information. Using the Object Explorer in SSMS 2012, we show in the Microsoft SQL training to create the first table and also assign a default value for a field. Using the built-in feature of object explorer you can display the data using SQL Select command. We continue moving forward and use TSQL code to make the second table which will house the Student information. Beside the CREATE TABLE command, we highlight constraint information for primary key, check constraints and unique constraints in SQL Server 2012. Constraints help you keep your database clean of typos and other errors. We end the video on MS SQL 2012 by showing you how to insert data into a table using the Insert SQL Command and check the data with SQL Select command.


“I am a new DBA using SQL Server. The demos and SQL scripts from your online training are quite handy in my day to day projects. Thanks Kash for putting it together. Regards”

Tom
Chicago

4. TSQL commands like SEQUENCE and CONCAT in MS SQL 2012

In this video training we cover new features in SQL Server 2012 from TSQL point of view.
We start New Transact SQL features in 2012 with Sequence. A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value before you insert child records. Next we go over new datetime functions in SQL Server 2012 training. We demonstrate DATEFROMPARTS, TIMEFROMPARTS and EOMONTH functions. The brand new OFFSET clause in MS SQL 2012 is discussed with Northwind database, here you can skip rows from your complete recordset by an Offset value. New Logical functions Select CHOOSE and Select IIF (Inline If) are covered during SQL Server 2012 tutorial by the Senior SQL DBA. For error handing in SQL 2012 Server, RAISERROR command is compared with new feature THROW SQL statement. Select CONCAT function in shown in SQL 2012demo, this one is especially helpful with Names. Using FORMAT command with GETDATE() function is helpful when working with different date formats. Finally PARSE() function is discussed for data type manipulation in SQL 2012.


5. SQL Server 2012 Database Relationships

We kick off this Microsoft SQL Server training session by covering terms like RDBMS, entity, field, ERD, Primary key (PK), Foreign key and a SQL Joins. There are three types of database relationships in SQL Server 2012. One to One, One to Many and Many to Many relationships. We go over each one of these relationships with examples in SQL Server 2012. In order to explain this better, we show you a demo of Customers and Orders tables and discuss the 1 – Many Relationships between them. Also we cover the database diagram feature in SQL 2012 which can help you understand the relationships between tables. We wrap this topic by going over Many – Many Relationships which is typically implemented using a junction table. Rest of the video tutorial is on a School database with Students, Teachers, Departments and Classes tables in SQL Server 2012. We highlight in this SQL training, the Primary key and Foreign keys among all these tables. Using SSMS we use TSQL to create the above mentioned tables and also populate them with data. Setting Primary and Foreign keys is a requirement before you can set up the relationships in SQL 2012 and we demonstrate that using SSMS and TSQL. We will also need the junction table Students_Classes to bring data from Students and Classes tables. Sub vid could be on create table and insert data

6. What is SQL Server 2012 Management Studio?

Here we are going to cover SQL Server Management Studio or SSMS in SQL 2012. This is the central location for database administration and development in MS SQL 2012. Using SSMS you can execute Transact SQL, create and modify stored procedures, views, tables and also have access to the Template Explorer in MS 2012. In order to launch SSMS, you can use Start > All Programs or the Command line utility. In addition to the database engine, you can also utilize SSMS to connect to other services like SQL Server Reporting Service (SSRS), SQL Server Integration Service (SSIS) and SQL Analysis Services (SSAS). We start off the SQL 2012 tutorial lesson with the File and the Edit menus. Using the File menu, you can connect to the abovementioned services.  Under the View menu option, you can connect to Object explorer, Registered servers and a host of other vital interfaces in SQL Server 2012. One option the Template Browser is a good way to learn TSQL especially if you’re new to SQL Server 2012.  Under Tools Menu, you would find SQL Server 2012 Profiler and Database Engine Tuning Advisor (DETA). The two tools can be a life saver when you are trying to troubleshoot slow queries and problematic stored procedure. Using the Registered servers, you can easily group your SQL Server 2012 servers into Production, Development and Staging.  There are many tabs in SSMS 2012, however the important ones are: Database, Security, Server Objects, Management and SQL agent. Our SQL Server 2012 tutorial demo includes looking at the tables and are now in database.

“The SQL Training and SQL Server 2012 scripts told me exactly what I needed to know and helped me get the job done. Did not spend days reading about it, followed along and just did it! Awesome training.”

// Zack – New York

7. Introduction to Transact SQL (TSQL)?

TSQL or Transact SQL is a powerful database language in SQL Server 2012. It includes DML (data manipulation language) and DDL (data definition language). We are going to look at Northwind database and use SQL Select, SQL Insert, SQL Delete and SQL Update statements. SQL Select is the most commonly used database command. We cover the syntax for SELECT statement in SQL 2012 with some examples on Customers table and Orders table using SQL 2012 Management Studio (SSMS). You can limit the number of column and rows using a WHERE clause plus sort them in ascending order. Next we go on to the SQL Operators in SQL Server 2012 including =, < (less than) and > (greater than) plus Logical operators like And/Or. INSERT SQL command is used to add new records to a table in SQL 2012. The syntax is INSERT INTO and we show you how to insert data into the Contractors table. The instructor uses SELECT * INTO to make a copy of a MS SQL 2012 table. Using a sub-select or sub query, you have the ability to insert multiple records from another table in SQL Server 2012. DELETE SQL command is used to remove data from a table. NOTE: Do not forget to use WHERE clause otherwise all rows will be terminated. Using a sub-select you can delete many rows from another table. We demonstrate using transactions in SQL Server 2012 like BEGIN TRAN .. COMMIT in conjunction with a Delete SQL command. Finally we go over the Update SQL query which can be used to update data in a SQL Server table. Note (use sub-query w/ delete on YouTube as a sep video)


8. Using Joins in SQL Server 2012 SQL Server 2012

A SQL join facilitates in bringing data together from various tables. In this SQL Server 2012 training, we go over basic concepts like Primary key, Foreign key, Inner and Outer joins in SQL Server 2012. An INNER JOIN is used to pull matching data from two tables; this concept is highlighted using Venn Diagrams. We do a demo on Inner join using Customers and Orders tables in SQL Server 2012. The two flavors of SQL syntax for joins are also discussed followed by another demo on School database. Next we cover OUTER JOINS in the SQL Server training, which help you find non-matched data between tables in SQL Server 2012. The two types of outer joins are Left outer join and Right outer join. Using a Left outer join in SQL 2012, we find out Customers that have not bought our super Products yet by using a WHERE clause Orders.Customerid=NULL. Using the School database, we find which Department has not offered any classes yet. We end this SQL 2012 Server tutorial by going over SQL Views. These are virtual tables that can help simplify complex code, control security and streamline your database code. Using the School database , we create a view between Departments and Classes tables in SQL Server 2012.

9. Implementing Security in SQL Server

There are many layers of Security in SQL Server including physical layer, surface area, object access etc. We start off this SQL Server 2012 training and discuss terminology like Principals, Roles, Securables and Microsoft recommended “Best Practices”. This SQL 2012 tutorial is “demo heavy” where we first create a SQL Server login and then a SQL database user using SSMS. The instructor also demonstrates how to take the same actions using TSQL with commands like CREATE LOGIN and CREATE USER. We also show you how to use security catalog views comprising of sys.server_principals and sys.database_principals. We move onto management of roles and groups in SQL Server 2012 which is the preferred method of controlling security in MS SQL 2012. The various server and database roles are defined and explained. We add Mary to the sysadmin role using new MS SQL 2012 command ALTER SERVER ROLE. Creating a role is demonstrated using SSMS and TSQL statement with SQL command CREATE ROLE. We proceed to add members to this role and make sure it is working via security catalog views.

10. New Features like Contained Database, FileTable ..

This video tutorial discusses new features in SQL Server 2012. We start with a Contained Database which is a specialty db that is isolated from other SQL Server databases. This addresses the problem of migrating a database from one SQL Server to another and run into issues like orphan user, SQL jobs and other server related objects. Next thing we discuss is SQL Server 2012 FileTable, which is a specific table that stores file and directory information from a network drive. This is built on FILESTREAM Technology from MS SQL 2008. Using FileTable, you can access metadata file level information which can be beneficial for applications manipulating input and output files with SQL Server. Next we move onto ColumnStore index that is specially built for high performance in a MSSQL data warehouse system. This uses column level information to store index data and is highly compressed. Migrating a SQL 2012 Server db to SQL Azure Cloud database has been an issue in the past. With this new release from Microsoft, you can do this from within SQL Server Management Studio.  We finish by discussing AlwaysOn which is an improvement over Database Mirroring and Clustering service for High Availability.

 

Follow us on: