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!