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!