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!