I used to be able to do the Common Table Expression in a single 1 hour session, but after writing the book on Common Table Expressions, I discovered that I just can’t fit it into an hour with the detail that Common Table Expressions deserve. I would rather go into depth so that everyone can master CTEs rather than just breezing over the highlights and leaving everyone wondering.
For future SQL Saturdays and other presentations the sessions have been broken out as follows. Currently I have proposed the following 2 sessions for SQL Saturday Denver.
Common Table Expressions – Introduction
Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expression session you will learn everything needed to start using CTEs for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTEs. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. The class is designed for people who haven’t used CTEs before, or for those who want to learn the basics of CTEs including data paging. This session pairs well with the Advanced Common Table Expression session.
Common Table Expressions – Advanced
You have been introduced to Common Table Expression, you understand the ;WITH syntax, but you want to know more. Learn how to recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE and DELETE data from CTEs. Some of the common use cases for CTEs will be covered including finding holes in patterns, finding and removing duplicate data, string parsing, and more. See how CTEs compare to SQL Server 2012 offset and fetch paging techniques. Get an in depth understanding of the performance behind a common table expression. Understand when the CTE is the right solution, and the wrong solution to use. Finally we will take a look at some classic recursive algorithms and how they can be implemented with CTEs. This session pairs well with the Introduction to Common Table Expression session.