Today Pluralsight published my new course, “SQL Server: Transact-SQL Common Table Expressions”.
This is a demo-centric, short course on how to create and use common table expressions (CTEs) correctly, for developers and DBAs from SQL Server 2005 onward. Areas I cover include:
- What are CTEs and Why Use Them?
- CTE Syntax
- Semicolon Requirements
- When Column Names Must Be Specified
- Execution Scope
- Multiple CTE Query Definitions
- Defining and Referencing Multiple CTEs
- Multiple References To the Same CTE
- CTEs with Data Modification Statements
- CTEs and Views
- CTEs and Stored Procedures
- CTEs and Triggers
- CTEs and User-Defined Functions
- Recursive CTE Examples
- Forward Referencing Restrictions
- ORDER BY restrictions
- INTO and CTE Restriction
- CTE Nesting Restriction
- OPTION Clause with Query Hints Restriction
- Max Recursion Limitation
- Late Filtering
- Non-Recursive Self-Join
- CTEs and Non-Deterministic Functions
- CTEs vs. Derived Table Code Clarity
- Windowing Functions, Predicates and CTEs
- Return Lineage Information
- Multiple Anchors
- Sequence Generation
- CTEs as Reference Table Replacements
- Windowing Functions and Data Modifications
- Referencing a Scalar Subselect in a Predicate
Much more to come from the SQLskills team!