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.
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)
However, you can use as many anchors and recursive elements as you want.
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().
11 thoughts on “Recursive common table expressions”
Pingback: Know your collation orders | Sunday morning T-SQL
Pingback: A function to calculate recurring dates | Sunday morning T-SQL
Pingback: About the spool operator | Sunday morning T-SQL
Pingback: Calculating business days and holidays | Sunday morning T-SQL
Pingback: Traversing parent-child relations | Sunday morning T-SQL
Pingback: Splitting a range string into a table « Sunday morning T-SQL
Pingback: An indented representation of a parent-child hierarchy « Sunday morning T-SQL
Pingback: Fun with random names | sqlsunday.com
Pingback: An alternative to data masking | sqlsunday.com
Pingback: Operations that need a serial plan | sqlsunday.com
Pingback: Optimizing a string split and search | sqlsunday.com