Geeks With Blogs
Random Musing Putting it together one piece at a time...

I thought this is worth noting. This is the way to generate 1 million records within 1 second in SQL Server 2005. I'll have the code pasted here so that I can have a reference the next time I need to use it. As Namwar Rizvi said, this code is proposed in SQL Server 2005 TSQL Querying by Microsoft press.


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

 

Posted on Thursday, August 23, 2007 10:44 AM Business Intelligence | Back to top


Comments on this post: T-SQL – 1 million records in 1 second

# re: T-SQL – 1 million records in 1 second
Requesting Gravatar...
Very nice, was busy doing research and this small query came handy. I recorded 6 seconds for a million records on my T3400 machine
Left by jay on Jun 20, 2008 5:40 AM

# re: T-SQL – 1 million records in 1 second
Requesting Gravatar...
Can u give me example from real table ? i rather confused :(
thanks,
ronald
Left by Ronald on Sep 01, 2008 9:57 AM

# re: T-SQL – 1 million records in 1 second
Requesting Gravatar...
Look dangerous
Left by Son on May 13, 2015 4:11 PM

Your comment:
 (will show your gravatar)


Copyright © Nestor | Powered by: GeeksWithBlogs.net