The problem is: how to get one million numbers to table with less time? We can solve this problem using different approaches but not all of them are quick. Let’s go now step by step and see how different approaches perform.
NB! The code samples here are not original ones but written by me as I wrote this posting.
Using WHILE
First idea for many guys is using WHILE. It is robust and primitive approach but it works if you don’t think about better solutions. Solution with WHILE is here.
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i += 1
end
When we run this code we have to wait. Well… we have to wait couple of minutes before SQL Server gets done. On my heavily loaded development machine it took 6 minutes to run. Well, maybe we can do something.
Using inline table
As a next thing we may think that inline table that is kept in memory will boost up performance. Okay, let’s try out the following code.
declare @t TABLE (number int)
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into @t values(@i)
set @i += 1
end
insert into numbers select * from @t
Read more: Gunnar Peipman's ASP.NET blog
Posts
