Friday morning at 8:00am at PASS Summit 15 in Seattle, I will be presenting my Advanced Common Table Expressions and Recursive Query Technique session.
Advanced Common Table Expressions
Here is the abstract:
You might have been introduced to Common Table Expressions (CTEs) and understand the WITH syntax, but want to know more. Learn how recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE, and DELETE data from CTEs? This session covers some common use cases for CTEs, including finding holes in patterns, finding and removing duplicate data, string parsing, and more. Get an in-depth understanding of the performance behind a CTE and learn when a CTE is the right (or wrong) solution. Finally, take a look at some classic recursive algorithms and how they can be implemented with CTEs.
For more details check out the session on the PASS Summit 2015 website.
There is a long history behind this presentation, it originally started as a presentation at SQL Saturday #108 in Redmond in 2012. From there I presented it at several other SQL Saturdays including Vancouver BC in 2012. After presenting on CTEs, at about 8 different SQL Saturdays, I eventually split the session into two, the Introduction to Common Table Expressions, and the Advanced CTEs. This was around the same time that I finished my book on Common Table Expressions which released the day of SQL Saturday 212 in Redmond in 2013. I found that once it was split into the Introduction to Common Table Expressions and Advanced Common Table Expressions that the Advanced session was much more popular.
I was able to practice this session recently at the 24 Hours of PASS in June of 2015, and at the Bellingham SQL Server Users Group meeting just 2 weeks ago.
The Advanced Common Table Expressions is where I cover fun things that you can do with Common Table Expressions, including some really cool formatting techniques for hierarchical data.
I hope to see you there.
- Common Table Expression (CTE) Month at SteveStedman.com (introduction)
- Sample Database for Common Table Expressions
- Writing Your First Common Table Expression with SQL Server
- Video: Writing Your First CTE with SQL Server
- Common Table Expressions – Terminating the Previous Statement
- CTE Scope
- Introduction to Recursive CTEs
- Recursive CTE for Dates In A Year
- Expanding on Recursive Dates CTE
- Multiple CTEs in a Query
- Nested CTEs
- Nested CTE’s Gone Wild – The Video
- CTE Data Paging
- CTE Data Paging in a Procedure
- What Do You Use CTEs for?
- Using a CTE in a Function to Split Up a Query String
- Using a CTE to Split a String Into Rows
- Can we use CTEs use in SSRS?
- CTE Hierarchy compared to the alternative
- Calculating Factorials with a Recursive CTE
- CTE – With An Insert Statement
- Fibonacci Sequence
- Generating a Tree Path with a CTE
- CTE Query Performance
- Multiple CTE’s in a single Query
- Recursive CTE’s
- Common Table Expressions Page
- A Fun Thanksgiving Day Query.
- Common Table Expressions