SQL Server 2005/2008 what's new part 2

SQL Saturday 53, takes place in 46 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.

Recursive Common Table Expressions

Recursive CTEs are like a normal Common Table Expressions, just with a little something extra to them. By default, you can have 100 levels of recursion before it chokes. Vast improvement over the 32 levels of default recursion allowed with procedure, function, trigger or nested views.

Syntax

The syntax is quite simple, see books online for the BNF. The following contrived queries count down from 100 and 101 to 0, endpoints inclusive.

-- This query demonstrates recursion by counting down from 100 to 0
;
WITH BASE AS
(
    -- Anchor query
    SELECT 100 AS anchor
    
    -- recursive query
    -- notice that I can reference BASE
    UNION ALL
    SELECT B.anchor -1 FROM BASE B WHERE B.anchor > 0
    
)
SELECT B.* FROM BASE B

-- This query demonstrates recursion by counting down from 101 to 0
-- The MAXRECURSION hint allows us to override the default for good or ill
;
WITH BASE AS
(
    -- Anchor query
    SELECT 101 AS anchor
    
    -- recursive query
    -- notice that I can reference BASE
    UNION ALL
    SELECT B.anchor -1 FROM BASE B WHERE B.anchor > 0
    
)
SELECT B.* 
FROM BASE B 
OPTION (MAXRECURSION 101)

What to notice

Recursive queries can be split into their non-recursive or anchor portion and the recursive portion. The anchor query which can be as complex as need be but it must is evaluated before the recursive portion begins. The recursive will execute until it either overflows the stack or returns an empty set a.k.a. meets the terminal condition. The default maximum recursion level for a CTE is 100 frames. The absolute maximum level of CTE recursion is 32,767 . If you're hitting this limit, congratulations. Now, rethink your query.

Availability

SQL Server 2005+