Introduction
Sometimes, a query needs to produce an exact amount of rows. There are several ways of doing it, creating a table containing enough rows, creating an UDF (User-Defined Function), using T-SQL loops and so on. This tip shows how a recursive CTE (Common Table Expression) can be utilized to do the job in a situation where the rows do not already exist in a table.
So, the Solution
Let's have a look at the SQL statement:
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < 10
)
SELECT RowNumber
FROM InfiniteRows;
GO
This statement generates a result of 10 rows as follows:
RowNumber
---------
1
2
3
4
5
6
7
8
9
10
So, what actually happens. The anchor statement produces one row having 1 as row number. The recursive part uses the CTE as the source table thus receiving rows from previous iteration as a result set. Now since this is a UNION the number of rows is growing by one on each iteration.
The restricting WHERE clause is placed in the recursive member definition in order to stop the recursion when the desired amount of rows has been generated.
So why the WHERE clause is inside the CTE, why not on the outside where the CTE is queried? This is because CTE works as a set. After the set is created, the result is passed to the executing statement. Moving the condition outside would cause an infinite loop. Let's try:
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
)
SELECT RowNumber
FROM InfiniteRows ir
WHERE ir.RowNumber < 10;
GO
Running that statement would cause an error:
Msg 530, Level 16, State 1, Line 22
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Now there's an interesting piece of information in the error message: Maximum recursion is 100. So, what if we want to generate 200 rows?
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < 200
)
SELECT RowNumber
FROM InfiniteRows;
GO
That causes the exact same message:
Msg 530, Level 16, State 1, Line 40
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
The 100 recursions is no hard limit. This behaviour can be changed using the MAXRECURSION hint within the statement. So if you want to raise the amount of recursions, the statement could look like the following:
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < 200
)
SELECT RowNumber
FROM InfiniteRows
OPTION (MAXRECURSION 1000);
GO
With the MAXRECURSION option, the statement is executed successfully and 200 rows are returned. Now what about larger amount of rows. What if we try to fetch 50'000 rows with a similar statement.
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < 50000
)
SELECT RowNumber
FROM InfiniteRows
OPTION (MAXRECURSION 50000);
GO
With the statement above, the following error is generated:
Msg 310, Level 15, State 1, Line 88
The value 50000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.
Again 32'767 is no hard limit for the result set size. It's just the maximum value for the MAXRECURSION hint. Using 0 for MAXRECURSION instructs SQL Server that there is no limit at all for the amount of recursions.
So, be careful with OPTION (MAXRECURSION 0): A small mistake in the SQL statement may easily cause an infinite loop!
Having that said, the following statement would return the desired 50'000 rows.
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < 50000
)
SELECT RowNumber
FROM InfiniteRows
OPTION (MAXRECURSION 0);
GO
The last variation is using a variable for the amount of rows. This makes it easier to define the limit for example from an application. For example, the following produces 1'000 rows.
DECLARE @RowAmount AS INT = 1000;
WITH InfiniteRows (RowNumber) AS (
SELECT 1 AS RowNumber
UNION ALL
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber < @RowAmount
)
SELECT RowNumber
FROM InfiniteRows
OPTION (MAXRECURSION 0);
GO
What About Performance
The CTE itself seems to work as a charm. Even with greater amounts (thousands) of rows, most of the execution time seems to be spent sending the data to the client application and showing them. I used SSMS as workbench with SET NOCOUNT ON. Running the statement with different amount of rows, the average execution times for different result set sizes looked like this:
As you can see, in my environment result sets with 20'000+ rows, the elapsed time starts to scatter. However, generating and showing 30'000 rows in less that half a second is sufficient in many cases.
Few Examples
The following tips show a few examples of using row generation with CTE:
References
The following links may be useful for further studies:
History
- 26th August, 2014: Created
