Remember all the times you’ve written long loop statements? Often, a recursive common table expression can be a much better solution, not to mention a lot more pleasing to the eye. The recursive CTE builds on the same basic framework as a regular CTE, except it consists of two parts: the anchor and the recursion.

What’s a recursive CTE?

Often, you need to produce a series of numbers, or repeat some type of action a lot of times. Recursion is when you perform an operation over and over, normally using the output of your operation as input for your next operation. The first operation is what’s called the anchor. Every subsequent operation is called a recursion.

To start off, here’s a very simple anchor (without the recursion).

WITH cte (n)
AS (SELECT 1 AS n)

SELECT * FROM cte;

Nothing funky going on here, it’s one row, one column. Now, if we were to add 1 to the “n” column..

WITH cte (n)
AS (SELECT 1 AS n)

SELECT n+1 FROM cte;

Still no surprises, right? But in these two queries, we have most of the code it takes to build a recursion. Now, here’s where the magic happens: If you move the outer SELECT statement into the common table expression itself, separated by a UNION ALL statement, it will look like this:

WITH cte (n)
AS (--- Anchor
    SELECT 1 AS n
    UNION ALL
    --- Recursion
    SELECT n+1 FROM cte WHERE n<10)

SELECT * FROM cte;

In short, the anchor produces the number 1. The recursion adds 1 to 1, producing 2. After that, the recursion will continue, until it does not generate any more rows. And that’s also the reason why we added “WHERE n<10”, to limit the number of recursions. Otherwise, it would go on until forever, or actually, until SQL Server has exhausted its maximum number of recursions, where you’d get the following error message:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been
exhausted before statement completion.

The limitation is there for a reason, obviously; if you write a statement that uses more than a 100 recursions, you should perhaps think about if you’ve chosen the right strategy with regards to performance. But it is possible to increase the limit – you can add the MAXRECURSION query hint at the end of the query (outside the CTE, at the very end of the statement)

WITH cte (n)
AS (--- Anchor
    SELECT 1 AS n
    UNION ALL
    --- Recursion
    SELECT n+1 FROM cte WHERE n<1000)

SELECT * FROM cte
OPTION (MAXRECURSION 1000);

The MAXRECURSION can be set from 1 to 32 767, or to 0 (allowing an infinite number of recursions). You can’t use the MAXRECURSION hint in a view.

Other limitations

There are a number of things you can’t use in the recursive part of a CTE, notably

• GROUP BY and aggregations
• LEFT, FULL and RIGHT JOINs
• Subqueries (though can reference another common table expression in the same statement)
• TOP

However, you can use as many anchors and recursive elements as you want.

Matching datatypes

In common table expressions, the UNION ALL operator does not perform any implicit data type conversions, so if the data types of the anchor and the recursion’s columns don’t match perfectly, you’ll get the following error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column
"xyz" of recursive query "cte".

The solution to this is simply to explicitly convert the data types of the columns in the recursion (and/or the anchor), using CAST() or CONVERT().