Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Generating Desired Amount of Rows in SQL using CTE

Rate me:
Please Sign up or sign in to vote.
4.64/5 (5 votes)
16 Sep 2014CPOL3 min read 62.8K   93   10   10
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:

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

SQL
-----------------------------------------
-- 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?

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

SQL
-----------------------------------------
-- 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.

SQL
-----------------------------------------
-- 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.

SQL
-----------------------------------------
-- 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.

SQL
-----------------------------------------
-- 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

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:

Image 1

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
QuestionQuestion Pin
Andrés Bernardo16-Oct-14 5:20
Andrés Bernardo16-Oct-14 5:20 
Questionworkaround for max recursion problem....and performance boost Pin
hariseos30-Sep-14 4:36
hariseos30-Sep-14 4:36 
AnswerRe: workaround for max recursion problem....and performance boost Pin
Wendelius30-Sep-14 6:02
mentorWendelius30-Sep-14 6:02 
GeneralRe: workaround for max recursion problem....and performance boost Pin
hariseos30-Sep-14 21:25
hariseos30-Sep-14 21:25 
GeneralRe: workaround for max recursion problem....and performance boost Pin
Wendelius1-Oct-14 0:02
mentorWendelius1-Oct-14 0:02 
GeneralRe: workaround for max recursion problem....and performance boost Pin
hariseos1-Oct-14 2:21
hariseos1-Oct-14 2:21 
QuestionNot generating unique number Pin
mukeshkane16-Sep-14 21:18
professionalmukeshkane16-Sep-14 21:18 
AnswerRe: Not generating unique number Pin
Wendelius16-Sep-14 22:15
mentorWendelius16-Sep-14 22:15 
GeneralRe: Not generating unique number Pin
mukeshkane16-Sep-14 22:52
professionalmukeshkane16-Sep-14 22:52 
GeneralRe: Not generating unique number Pin
Wendelius19-Sep-14 5:01
mentorWendelius19-Sep-14 5:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.