Generating 1 million rows in less than a second

This post was written by namwar on August 4, 2007
Posted Under: Uncategorized

If you are a SQL Query developer like me than you must came across the situation where you need an auxiliary table of numbers which contain just one column and rows like 1,2,3…….n
There are so many uses of this table like generating dummy data by cross joining this table to another table etc.
Following is a very quick and efficient way of generating 1 million rows in less than a second by using Common Table Expressions or CTE and Recursion of SQL Server. This logic is actually proposed in the book called SQL Server 2005 TSQL Querying by Microsoft press.

Following is the code:

–Declare a variable to hold the
–count of rows to be generated
Declare @p_NumberOfRows Bigint

–We need 1 million rows
Select @p_NumberOfRows=100000;

With Base As
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
Expand As
Select 1 as C
From Base as B1, Base as B2
Nums As
Select Row_Number() OVER(ORDER BY C) As n
From Expand
Select n from Nums Where n<=@p_NumberOfRows
–Remove Maximum Recursion level constraint
OPTION (MaxRecursion 0);

Add a Comment

required, use real name
required, will not be published
optional, your blog address

Previose Post: I am back!