In this course, you’ll learn about some advanced T-SQL statements and operators that can dramatically simplify individual statements to perform complex operations and queries. The APPLY operator lets you feed each row from the result set of one query to a second query. The MERGE statement lets you perform multiple actions with a single statement. You can use grouping sets to group data multiple ways in a single group by clause. You’ll learn about all these advanced query techniques, as well as how to safely execute dynamic SQL statements, and more. Then you’ll learn about how SQL server executes queries, taking a behind the scenes look what it takes to compile your beautiful T-SQL code in to a structure that SQL server can execute. You’ll learn about the stages of query compilation and explore the mysteries world of statics that are so critical to well performing queries. Then you’ll learn about execution plans and how to analyze them as well as what some of the most common operators do.
This course assumes that you have a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals. You should know the fundamentals of Transact-SQL to write queries to retrieve and update data, and how to execute scripts using the query editor in Management Studio. The course spends a lot of time deep in T-SQL code, so you should feel comfortable writing basic queries that involve joins of multiple tables, as well as using built-in T-SQL functions and other code modules. It will be very helpful, but not absolutely necessary, to have experience with .NET development using Visual Studio 2010 or 2012 for the portions of the course that deal with SQL Server Data Tools.