In my opinion, Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. A CTE is a “temporary result set” that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

CTE basics

The concept behind a CTE is simplicity itself. Consider the following statement:

This defines a CTE called MyCTE. In brackets after the as keyword is the query that defines the CTE. The subsequent query references our CTE, in this case simply returning the string “hello”.

Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived table, a CTE can be referenced multiple times in the same query. So, we now we have a way of calculating percentages and performing arithmetic using aggregates without repeating queries or using a temp table:

This returns (on my system):

Note that although this has only referenced sysobjects once in the CTE, the query plan will confirm that sysobjects is actually scanned 3 times – each aggregate in the result set causes an additional scan. As a result, it would still be more efficient to accumulate the values in a temporary table or table variable if you are accessing large tables.

CTE and recursion

More interesting, in my opinion, is the use of recursion with CTEs. The table defined in the CTE can be referenced in the CTE itself to give a recursive expression, using union all:

The query:

select x = convert(varchar(8000),’hello’)

is called the anchor member. This is executed in the first pass and will populate the CTE with the result, in this case hello. This initial CTE is repeatedly executed until the complete result set is returned. The next entry:

select x + ‘a’ from MyCTE where len(x) < 100

is a recursive member as it references the CTE, MyCTE. The recursive member is executed with the anchor member output to give helloa. The next pass takes helloa as input and returns helloaa, and so on so that we arrive at a CTE populated with rows as follows:

The recursion will terminate when the recursive member produces no rows – in this case recursion stops when the length of x equals 99 – or when the recursion limit is reached (more about that later). The CTE is then output by the following statement:

select x from MyCTE
order by x

There are a few interesting issues associated with even this simple CTE usage. Note that the anchor member populates the CTE with a varchar(8000). Had the convert not been included then you would expect the datatype of x to be defined by that of the anchor member (varchar(5)) and to give an error when trying to insert the longer recursive entries. This does indeed happen – but you would expect varchar(1000) to be fine. Not so. This still produces the error that the datatypes don’t match. In fact, you should always cast the recursive member output to be the same as the anchor member:

But why, then, did the code work with varchar(8000)? This looks like a bug. As far as I can tell varchar(8000) and nvarchar(4000) seem to be the only values that work. Varchar(max) and varchar(7999) give an error as do all the other values that I have tried.

Multiple anchor members

Any entry that does not reference the CTE will be considered an anchor member so we can also include multiple anchor members using a union all:

This adds two rows from the anchor members and hence the recursive member acts on two rows for each pass to produce the output:

The first recursive pass produces the output “helloa” and “goodbyea”. The second produces “helloaa” and “goodbyeaa”. The third produces “helloaaa” and “goodbyeaaa”. For subsequent passes the string containing “goodbye” is too long for the check len(x)<10 but recursion continues as long as some output is produced – so we get more entries from hello than goodbye.

Multiple recursive members

We can also include multiple recursive members to produce extra rows at each pass:

This produces the result:

In order to understand this output, you need to remember that the input to each pass is the output from the previous pass. On the first recursive pass the input is the row from the anchor member. This produces two rows – one from each recursive member. On the next pass the input is the two rows output from the previous pass. Each recursive member outputs two rows producing four in total. In other words, the number of rows output doubles with each pass.

We can modify the output by limiting the rows on which the recursive members act. For example:

giving:

Another method is to flag the recursive members:

This gives the same result as above. It forces each recursive member to act only on the anchor member output and on any output that it itself has produced. This is at the expense of including the redundant data column “i” in the CTE but it does make the code a lot more readable. Using a similar technique we can output a value to indicate which pass produces each row:

This returns the following, r1 giving the pass number for the first recursive member and r2 for the second:

This is very useful for debugging and also for detecting how a CTE is processed and for controlling the number of passes for each recursive member. For instance:

Here I have terminated the first recursive member after two iterations and the second after five, giving:

Note that recursion continues until a pass produces no output – although the first recursive member terminates early recursion continues until both recursive members produce no output.

Recursion limit

In order to demonstrate the recursion limit, we start by producing a series of numbers in a CTE:

This happily produces the numbers 1 to 100 (a tally table). However, try increasing the recursion limit as follows:

You will receive the following error:

“The statement terminated. The maximum recursion 100 has been exhausted before statement completion“

It sounds like there is a limit of 100 for recursion but luckily this is just the default limit. For development this is very useful; to save time and space consider setting it lower for first attempts.

The maximum number of recursions can be set via the maxrecursion option, up to a maximum of 32767 (wouldn’t it be nice, though, if the error message suggested that the recursion limit should be increased?):

Uses for Common Table Expressions

Finally, let’s review some of the more interesting applications of CTEs.