Expanded

SQL Server: Transact-SQL Common Table Expressions

By Joe Sack
Learn how to create and use common table expressions (CTEs) correctly, for developers and DBAs from SQL Server 2005 onward
Get started
Course info
Rating
(387)
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 20m
Table of contents
Course Introduction
Course Introduction 1m Course Structure 1m
CTE Fundamentals
Module Introduction 1m What are CTEs and Why Use Them? 1m CTE Syntax 1m Demo: Non-Recursive CTE Example 3m Demo: Semicolon Requirements 1m Demo: When Column Names Must Be Specified 2m Demo: Execution Scope 3m Demo: Multiple CTE Query Definitions 1m Demo: Defining and Referencing Multiple CTEs 1m Demo: Multiple References To the Same CTE 3m Demo: CTEs with Data Modification Statements 2m Demo: CTEs and Views 1m Demo: CTEs and Stored Procedures 1m Demo: CTEs and Triggers 2m Demo: CTEs and User-Defined Functions 3m Demo: Recursive CTE Example 4m Module Summary 0m
CTE Restrictions
Module Introduction 1m Demo: Forward Referencing Restrictions 2m Demo: ORDER BY restrictions 3m Demo: INTO and CTE Restriction 1m Demo: CTE Nesting Restriction 1m Demo: OPTION Clause with Query Hints Restriction 1m Demo: Max Recursion Limitation 3m Is it a Bug? 1m Demo: Late Filtering 5m Demo: Non-Recursive Self-Join 4m Demo: CTEs and Non-Deterministic Functions 3m Module Summary 1m
CTE Usage Scenarios
Module Introduction 1m Demo: CTEs vs. Derived Table Code Clarity 3m Demo: Windowing Functions, Predicates and CTEs 3m Demo: Return Lineage Information 3m Demo: Multiple Anchors 3m Demo: Sequence Generation 2m Demo: CTEs as Reference Table Replacements 1m Demo: Windowing Functions and Data Modifications 2m Demo: Referencing a Scalar Subselect in a Predicate 4m Summarizing CTE Usage Scenarios 2m Module Summary 1m Course Summary 0m
Description
Course info
Rating
(387)
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 20m
Your 10-day individual free trial includes:

Expanded library

This course and over 7,000+ additional courses from our full course library.

Hands-on library

Practice and apply knowledge faster in real-world scenarios with projects and interactive courses.
*Available on Premium only
Start a Premium 10-day free trial
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
Joe Sack
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
SQL Server: Common Query Tuning Problems and Solutions - Part 2
Intermediate
2h 17m
Aug 11, 2014
SQL Server: Common Query Tuning Problems and Solutions - Part 1
Intermediate
2h 30m
Jul 7, 2014
More courses by Joe Sack

You're the smartest person in the room

PROVE IT

Your team won't just close the skills gap

THEY'LL HURDLE IT

Course info
Rating
(387)
Level
Intermediate
Updated
Feb 14, 2014
Duration
1h 20m
Pluralsight IQ
Know exactly where everyone on your team stands with assessments and analytics.
Try our full library including this course free for your whole team for 14-days.