SQL Server: Transact-SQL Common Table Expressions
By Joe Sack
Learn how to create and use common table expressions (CTEs) correctly, for developers and DBAs from SQL Server 2005 onward
Course info
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 21m
Table of contents
Course Introduction
CTE Fundamentals
Module Introduction
1m
What are CTEs and Why Use Them?
1m
CTE Syntax
1m
Demo: Non-Recursive CTE Example
3m
Demo: Semicolon Requirements
1m
Demo: When Column Names Must Be Specified
2m
Demo: Execution Scope
3m
Demo: Multiple CTE Query Definitions
1m
Demo: Defining and Referencing Multiple CTEs
1m
Demo: Multiple References To the Same CTE
3m
Demo: CTEs with Data Modification Statements
2m
Demo: CTEs and Views
1m
Demo: CTEs and Stored Procedures
1m
Demo: CTEs and Triggers
2m
Demo: CTEs and User-Defined Functions
3m
Demo: Recursive CTE Example
4m
Module Summary
0m
CTE Restrictions
Module Introduction
1m
Demo: Forward Referencing Restrictions
2m
Demo: ORDER BY restrictions
3m
Demo: INTO and CTE Restriction
1m
Demo: CTE Nesting Restriction
1m
Demo: OPTION Clause with Query Hints Restriction
1m
Demo: Max Recursion Limitation
3m
Is it a Bug?
1m
Demo: Late Filtering
5m
Demo: Non-Recursive Self-Join
4m
Demo: CTEs and Non-Deterministic Functions
3m
Module Summary
1m
CTE Usage Scenarios
Module Introduction
1m
Demo: CTEs vs. Derived Table Code Clarity
3m
Demo: Windowing Functions, Predicates and CTEs
3m
Demo: Return Lineage Information
3m
Demo: Multiple Anchors
3m
Demo: Sequence Generation
2m
Demo: CTEs as Reference Table Replacements
1m
Demo: Windowing Functions and Data Modifications
2m
Demo: Referencing a Scalar Subselect in a Predicate
4m
Summarizing CTE Usage Scenarios
2m
Module Summary
1m
Course Summary
0m
Description
Course info
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 21m
Description
Common table expressions are a useful and versatile T-SQL query construct and this demo-centric course shows how to correctly define and use CTEs, what restrictions there are for using them, and some common usage scenarios. This course is perfect for developers, DBAs, and anyone responsible for writing Transact-SQL code, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onward.
About the author
About the author
Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.
More from the author
More courses by Joe Sack