SQL Tips by Namwar Rizvi

August 4, 2007

Generating 1 million rows in less than a second

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);

Advertisements

1 Comment »

  1. Select @p_NumberOfRows=100000; – it’s 100k, not a million.
    I tried it and million rows are generated in 10-11 secs.

    Comment by EvAlex — February 5, 2012 @ 11:48 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: