SQL Server: Transact-SQL Common Table Expressions

Learn how to create and use common table expressions (CTEs) correctly, for developers and DBAs from SQL Server 2005 onward
Course info
Rating
(369)
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 20m
Table of contents
Course Introduction
CTE Fundamentals
CTE Restrictions
CTE Usage Scenarios
Description
Course info
Rating
(369)
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 20m
Description

Common table expressions are a useful and versatile T-SQL query construct and this demo-centric course shows how to correctly define and use CTEs, what restrictions there are for using them, and some common usage scenarios. This course is perfect for developers, DBAs, and anyone responsible for writing Transact-SQL code, from complete beginners through to those with more experience. The information in the course applies to all versions from SQL Server 2005 onward.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

Course Introduction
Hi. This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Transact-SQL Common Table Expressions course and you're watching the introduction where I'll briefly describe what to expect over the modules that make up this course. SQL Server 2005 introduced common table expressions or CTEs for short and CTEs are similar to view or derived tables and allow you to define one or more queries that can be referenced within a data manipulation language statement and unlike derived tables, CTEs allow you to reference the definition multiple times within a statement without having to redefine the query each time. And unlike views, CTEs don't have to be persistent as a database object in order to be referenced. One big advantage of CTEs is that they can be used to increase the overall readability of your T SQL code and often times you can convert a complex query into smaller chunks of logic that end up being far more human readable and therefore more supportable. In this course I'll cover the fundamentals behind CTEs and present several usage scenarios. Along the way I'll also point out other strengths and weaknesses of the CTE feature.

CTE Fundamentals
Hi. This is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Transact SQL Common Table Expressions course and you're watching module 2, where we'll learn about the fundamentals of using common table expressions. In this module we'll be covering the fundamentals behind common table expressions and I'll show you both the non-recursive and recursive flavors of this feature. We'll discuss considerations around CTE scoping and references, how to use CTEs in conjunction with data modification statements, and then we'll review how to use CTEs within the context of stored procedures, views, functions, and triggers.

CTE Restrictions
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Transact SQL Common Table Expressions course and you're watching module 3 where we'll cover some of the restrictions you should be aware of when using CTEs in your T-SQL code. In this module we'll be covering various CTE-related restrictions that could influence your performance or functionality. We'll cover restrictions around forward references, ORDER BY restrictions, limitations around using INTO, CTE nesting limitations, restrictions around the OPTION clause with query hints, recursion limitations and how to get around them, and we'll also cover other performance issues you may encounter.

CTE Usage Scenarios
Hi. This is Joe Sack for SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server Transact SQL Common Table Expressions course and you're watching module 4, where we'll cover various scenarios where common table expressions can be useful. In this module we'll coverage various CTE usage scenarios including how to use CTES in place of derived tables, use CTEs in order to apply predicates on windowing function values, return recursive lineage information, define multiple anchors for recursive query, generate sequence values, return lookup table values without having to define a permanent lookup table, perform data modifications based on windowing function values, and lastly, we'll cover how to use CTEs to reference a scalar sub select in the predicate.