Common Table Expressions - Advanced

Dive into advanced Common Table Expressions (CTEs) in this 53-minute conference talk from PASS Data Community Summit. Explore recursive queries and hierarchical data display using CTEs. Learn how to perform INSERT, UPDATE, and DELETE operations on CTEs. Discover common use cases including finding pattern holes, removing duplicates, and string parsing. Compare CTEs to SQL Server 2012 offset and fetch paging techniques. Gain in-depth understanding of CTE performance, identifying when to use them effectively. Examine classic recursive algorithms implemented with CTEs. Walk away with practical CTE samples to enhance your T-SQL query skills.

Syllabus

Intro
Technical Assistance
SUMMIT 2015
Advanced Common Table Expressions
Recursion Overview
Recursive Terminology
Demo: Recursive CTE
Recursive Demo
Recursive CTE Notes
Hierarchy with multiple recursive queries is possible
Hierarchy with advanced formatting
Recursive Heirarchy Demo
Manipulating Data
Deleting From a CTE
Inserting to a CTE
Updating a CTE
INSERT, UPDATE, DELETE Notes
CTE Common Use Case 2
CTE Performance Considerations
Nested CTES
Recursive Performance
Deep Recursion
Classic Recursive Algorithms