Expanded Library
SQL Server: Transact-SQL Common Table Expressions
Learn how to create and use common table expressions (CTEs) correctly, for developers and DBAs from SQL Server 2005 onward
What you'll learn
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.
Table of contents
Course Introduction
2mins
CTE Fundamentals
30mins
- 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
26mins
- 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
25mins
- 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