Unleashing Common Table Expressions on SQL Server
Have you ever wanted to create a recursive query but didn't see how to do it? Do you have complex query with logs of subqueries that could be cleaned up with a CTE?
Through this Common Table Expressions course you will learn everything needed to start using CTE's.
Includes a Free copy of Steve's Common Table Expressions learning manual with over 250 pages (read more below).
You will learn to use CTE's for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE's.
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.
Learn how recursive CTE’s perform better than other methods to create hierarchical results. See how CTE’s compare to SQL Server offset and fetch paging techniques.
*** This presentation is targeted for programmers who are familiar with TSQL, subqueries, and JOIN techniques.
You will learn to:
- Learn hierarchical queries including tree path’s that can be created easily and run efficiently in TSQL. Including several SQL CTE examples.
- Learn how to create a recursive CTE query to perform complex calculations. Including samples of recursive algorithms for Fibonacci sequences and calculation of Factorial in a single SQL statement
- Learn how CTE’s compare in performance to other more cumbersome ways of calculating tree paths or hierarchy. Also learn how CTE’s for data paging compare to the windowing features.
*** See curriculum below for more details.
Free download with Purchase of this Course!
- Over 250 pages of content all about TSQL and Common Table Expressions for SQL Server.
This book will show some of the more interesting things you can do with a CTE. We will cover how to use the DELETE, UPDATE, and INSERT statements with a CTE, and will discover what is good and bad with CTEs when it comes to performance and how to avoid performance pitfalls.
Once you have mastered the CTE you may never look at a query the same again.
- CHAPTER 1. MEMORY TABLES
- CHAPTER 2. WHAT IS A COMMON TABLE EXPRESSION?
- CHAPTER 3. CTES INSTEAD OF DERIVED TABLES
- CHAPTER 4. RECURSIVE CTES
- CHAPTER 5. HIERARCHICAL CTES
- CHAPTER 6. MULTIPLE CTES IN A QUERY
- CHAPTER 7. DATA PAGING WITH CTES
- CHAPTER 8. MANIPULATING DATA
- CHAPTER 9. CTES IN FUNCTIONS, STORED PROCEDURES AND VIEWS
- CHAPTER 10. COMMON USE CASES
- CHAPTER 11. CTE PERFORMANCE CONSIDERATIONS
- CHAPTER 12. CTES FOR GEEKS – CLASSIC RECURSIVE ALGORITHMS
Curriculum
(click arrow below to view full curriculum)