Recursive CTE’s

As part of my planning for the SQL Saturday Presentation in Vancouver I am creating an hour long presentation on Common Table Expressions.

The easiest way to do a recursive query in SQL server is to use a recursive CTE (Common Table Expression).

What is a Common Table Expression in SQL Server?

• Similar to the ease of a temporary table without the performance problems of a temp table.
• Temporary Named Result Set
• Acts like a temporary view
• Can be use to
• Create a recursive query
• Simplify a query by using a result set multiple times
• Self JOIN a subquery without running the subquery twice

Here is a transact SQL sample based on the AdventureWorksLt database demonstrating a CTE for a recursive query.

WITH CategoryCTE(CategoryId, Name, Level) AS (
SELECT ProductCategoryId AS CatId,
Name, 0 as Level
FROM saleslt.ProductCategory
WHERE ParentProductCategoryId is NULL
UNION ALL -- and now for the recursive part 
SELECT c.ProductCategoryId AS CatId,
c.Name, ccte.CategoryId + 1 as Level
FROM saleslt.ProductCategory c
INNER JOIN CategoryCTE as ccte
ON ccte.CategoryId = c.ParentProductCategoryID
)
SELECT *
FROM CategoryCTE
ORDER BY Level, Name;

 

CTE’s and Recursive CTE’s were first introduced in SQL Server 2005, and are available in 2008 and 2008R2 with the same syntax.

For another example of CTE’s (non-recursive) take a look at the query behind the Backup Set SQL Server Health Report.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!