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

Course description

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.

Each LearnNowOnline training course is made up of Modules (typically an hour in length). Within each module there are Topics (typically 15-30 minutes each) and Subtopics (typically 2-5 minutes each). There is a Post Exam for each Module that must be passed with a score of 70% or higher to successfully and fully complete the course.

Prerequisites

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.

Meet the expert

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

Video Runtime

184 Minutes

Time to complete

704 Minutes

Course Outline

APPLY, MERGE and Grouping Sets

Using APPLY (14:51)

• Introduction (01:08)
• Using APPLY (00:36)
• Demo: APPLY (04:20)
• Demo: Why Use APPLY (03:28)
• Demo: APPLY with User Functions (02:28)
• Demo: APPLY with For XML Path (02:11)
• Summary (00:37)

MERGE Statement (23:13)

• Introduction (00:46)
• Insert and Update with MERGE (01:09)
• MERGE Clauses (00:35)
• WHEN Clause (02:31)
• Demo: Setting up the Data (04:14)
• Demo: MERGE Statement (03:05)
• Demo: Explaining MERGE (02:03)
• Demo: Another MERGE Example (04:18)
• Demo: Testing MERGE (04:06)
• Summary (00:19)

Creating Recursive Queries (07:37)

• Introduction (00:53)
• Demo: The Old Way (02:11)
• Demo: Common Table Expression (02:51)
• Demo: Execution Plans (01:20)
• Summary (00:21)

Grouping Sets (14:36)

• Introduction (00:42)
• Grouping Data (00:47)
• Demo: Base Query (02:55)
• Demo: Group By Sets (01:29)
• Demo: Old Way (03:51)
• Demo: Grouping Sets (01:49)
• Demo: Execution Plans (02:44)
• Summary (00:16)

ROLLUP, CUBE and Pivot Queries

ROLLUP and CUBE Operators (11:43)

• Introduction (00:31)
• ROLLUP Operator (00:29)
• Demo: GROUP BY ROLLUP (02:53)
• CUBE Operator (00:49)
• Demo: CUBE Operator (01:55)
• Identify with GROUPING_ID (01:02)
• Demo: GROUPING_ID with ROLLUP (01:40)
• Demo: GROUPING_ID with CUBE (00:58)
• Demo: GROUPING_ID (00:58)
• Summary (00:23)

Creating Pivot Queries (14:23)

• Introduction (00:48)
• Rotating Column Data (00:29)
• Demo: Pivoting Data (03:32)
• Demo: Pivot Operator (04:03)
• Demo: Pivot Using Strings (01:24)
• Unpivoting Data (00:52)
• Demo: Unpivoting Data (02:51)
• Summary (00:21)

Executing Dynamic SQL (28:13)

• Introduction (00:59)
• Demo: Issue with Pivot Operator (01:42)
• Demo: Dynamic Pivot Statement (03:35)
• Parameters with sp_executesql (01:02)
• Demo: Dynamic SQL (05:03)
• Using QUOTENAME (00:25)
• Demo: QUOTENAME (01:47)
• Demo: QUOTENAME and Attacks (03:26)
• Demo: QUOTENAME Optional Param (01:17)
• Signing Stored Procedures (01:44)
• Demo: Sign Stored Procedure (03:52)
• Demo: Using a Certificate (02:49)
• Summary (00:26)

Query Execution

Complex Query Execution (25:52)

• Introduction (01:54)
• How SQL Server Executes Queries (00:48)
• Stages of Compilation (02:36)
• Execution Plan Caching (02:45)
• Plan Adequacy (01:13)
• Statistics (02:37)
• Statistics Data (02:00)
• Demo: Statistics Query (01:46)
• Demo: DBCC SHOW_STATISTICS (04:23)
• Manual Statistics Update (02:09)
• Statistics IO (01:04)
• Demo: Statistics IO (02:13)
• Summary (00:19)

Using Execution Plans (16:21)

• Introduction (01:11)
• Estimated vs. Actual Plans (01:49)
• Demo: Set XML (04:00)
• Demo: Turn on Execution Plans (02:15)
• Demo: Execution Plans (03:21)
• Demo: Execution Plan Diagram (03:16)
• Summary (00:27)

Execution Plan Operators (11:54)

• Introduction (00:38)
• Common Execution Plan Operators (00:37)
• Common Access Operators (01:14)
• Demo: Common Access Operators (03:09)
• Demo: Covering Index (04:02)
• Demo: Non-Clustered Index (01:50)
• Summary (00:21)

Common Join Operators (15:30)

• Introduction (00:37)
• Demo: Nested Loops Operator (03:39)
• Demo: Hash Match Operator (04:31)
• Demo: Sort Operator (03:09)
• Demo: GROUP BY Clause (02:50)
• Summary (00:41)