SQL 2014 Developer, Part 8 of 13: Advanced T-SQL Queries

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.

Lesson 1:

• Using APPLY
• Demo: APPLY
• Demo: Why Use APPLY
• Demo: APPLY with User Functions
• Demo: APPLY with For XML Path.

Lesson 2:

• Insert and Update with MERGE
• MERGE Clauses
• WHEN Clause
• Demo: Setting up the Data
• Demo: MERGE Statement
• Demo: Explaining MERGE
• Demo: Another MERGE Example
• Demo: Testing MERGE.

Lesson 3:

• Demo: The Old Way
• Demo: Common Table Expression
• Demo: Execution Plans.

Lesson 4:

• Grouping Data
• Demo: Base Query
• Demo: Group By Sets
• Demo: Old Way
• Demo: Grouping Sets
• Demo: Execution Plans.

Lesson 5:

• ROLLUP Operator
• Demo: GROUP BY ROLLUP
• CUBE Operator
• Demo: CUBE Operator
• Identify with GROUPING_ID
• Demo: GROUPING_ID with ROLLUP
• Demo: GROUPING_ID with CUBE
• Demo: GROUPING_ID.

Lesson 6:

• Rotating Column Data
• Demo: Pivoting Data
• Demo: Pivot Operator
• Demo: Pivot Using Strings
• Unpivoting Data
• Demo: Unpivoting Data.

Lesson 7:

• Demo: Issue with Pivot Operator
• Demo: Dynamic Pivot Statement
• Parameters with sp_executesql
• Demo: Dynamic SQL
• Using QUOTENAME
• Demo: QUOTENAME
• Demo: QUOTENAME and Attacks
• Demo: QUOTENAME Optional Param
• Signing Stored Procedures
• Demo: Sign Stored Procedure
• Demo: Using a Certificate.

Lesson 8:

• How SQL Server Executes Queries
• Stages of Compilation
• Execution Plan Caching
• Plan Adequacy
• Statistics
• Statistics Data
• Demo: Statistics Query
• Demo: DBCC SHOW_STATISTICS
• Manual Statistics Update
• Statistics IO
• Demo: Statistics IO.

Lesson 9:

• Estimated vs. Actual Plans
• Demo: Set XML
• Demo: Turn on Execution Plans
• Demo: Execution Plans
• Demo: Execution Plan Diagram.

Lesson 10:

• Common Execution Plan Operators
• Common Access Operators
• Demo: Common Access Operators
• Demo: Covering Index
• Demo: Non-Clustered Index.

Lesson 11:

• Demo: Nested Loops Operator
• Demo: Hash Match Operator
• Demo: Sort Operator
• Demo: GROUP BY Clause.