12,299,790 members (55,393 online)
Tip/Trick
alternative version

18.8K views
9 bookmarked
Posted

# Generating Desired Amount of Rows in SQL using CTE

, 16 Sep 2014 CPOL
 Rate this:
How to generate desired amount of rows in SQL Server using CTE

## 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:

```-----------------------------------------
-- Statement that produces 10 rows
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < 10
)
-- Statement that executes the CTE
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:

```-----------------------------------------
-- Infinite loop because of
-- the condition placement
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
)
-- Statement that executes the CTE
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?

```-----------------------------------------
-- Try to generate 200 rows
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < 200
)
-- Statement that executes the CTE
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:

```-----------------------------------------
-- Use MAXRECURSION option
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < 200
)
-- Statement that executes the CTE
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.

```-----------------------------------------
-- Try to fetch 50'000 rows
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < 50000
)
-- Statement that executes the CTE
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.

```-----------------------------------------
-- Fetch 50'000 rows with infinite option
-----------------------------------------
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < 50000
)
-- Statement that executes the CTE
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.

```-----------------------------------------
-- Use parameter for the limit
-----------------------------------------
DECLARE @RowAmount AS INT = 1000;
WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1    AS RowNumber
FROM   InfiniteRows a
WHERE  a.RowNumber < @RowAmount
)
-- Statement that executes the CTE
SELECT RowNumber
FROM   InfiniteRows
OPTION (MAXRECURSION 0);
GO```

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

## Share

 Architect Finland
No Biography provided

## You may also be interested in...

 First Prev Next
 Question Andrés Bernardo16-Oct-14 5:20 Andrés Bernardo 16-Oct-14 5:20
 workaround for max recursion problem....and performance boost hariseos30-Sep-14 4:36 hariseos 30-Sep-14 4:36
 Re: workaround for max recursion problem....and performance boost Mika Wendelius30-Sep-14 6:02 Mika Wendelius 30-Sep-14 6:02
 Re: workaround for max recursion problem....and performance boost hariseos30-Sep-14 21:25 hariseos 30-Sep-14 21:25
 Re: workaround for max recursion problem....and performance boost Mika Wendelius1-Oct-14 0:02 Mika Wendelius 1-Oct-14 0:02
 Re: workaround for max recursion problem....and performance boost hariseos1-Oct-14 2:21 hariseos 1-Oct-14 2:21
 Not generating unique number mukeshkane16-Sep-14 21:18 mukeshkane 16-Sep-14 21:18
 Re: Not generating unique number Mika Wendelius16-Sep-14 22:15 Mika Wendelius 16-Sep-14 22:15
 Re: Not generating unique number mukeshkane16-Sep-14 22:52 mukeshkane 16-Sep-14 22:52
 Re: Not generating unique number Mika Wendelius19-Sep-14 5:01 Mika Wendelius 19-Sep-14 5:01
 Last Visit: 31-Dec-99 18:00     Last Update: 28-May-16 23:59 Refresh 1