Wednesday 8 July 2009

Generating a list of sequential numbers in TSQL

Generating a list of sequential numbers.

A really interesting post, comparing techniques...

http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table

 The fastest (method 7) is reproduced below ;
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO NumbersTest
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest

No comments: