SQL Server: Transact-SQL Common Table Expressions

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.