gbritton1 (12/4/2013)
I noticed the examples in the book for creating a Numbers table. Of course they all work fine! I would like to contribute a variant that I happen to like. It depends on the (undocumented but heavily used) table master.dbo.spt_values, though any sufficiently-large table will do:
SELECT TOP <quantity desired> IDENTITY(int, 0,1) AS n
INTO #Numbers
FROM master.dbo.spt_values v1, master.dbo.spt_values v2;
ALTER TABLE #Numbers ADD PRIMARY KEY CLUSTERED (n);
SELECT COUNT(*) from #Numbers;
This gave me 5503716 natural numbers to use on my SQL Server 2005 instance and 6290064 on my SQL Server 2008 R2 instance. I believe that it is also much faster than the while-loop approach. Since the table is populated using a cartesian product, one can rapidly build tables of any size, subject to disk space limitations of course. You can fill in <quantity desired> with the upper limit of the range you need.
Indeed, this variant is faster than the one in the book. However, I would not use it for two reasons:
1. This method is based on system tables (undocumented spt_values). I also saw a variation of this method using sys.objects. I would not base my solution on anything which does not belong to my application and over which I do not have full control. For example: the content of these sys tables may change, the tables may not be valid anymore in new version of SQL, etc.
2. This method is still slow. It creates and populates #Numbers table very fast, but then it spends a lot of time to add PRIMARY KEY. If you want to play with IDENTITY, I would recommend the following approach:
CREATE TABLE Numbers (Number int identity(1,1) NOT NULL PRIMARY KEY, T bit NULL);
WITH
T1(T) AS (SELECT T FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(T)) --10 rows
,T2(T) AS (SELECT A.T FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows
,T3(T) AS (SELECT A.T FROM T2 AS A CROSS JOIN T2 AS B CROSS JOIN T2 AS C) --1,000,000,000 rows
INSERT INTO dbo.Numbers(T)
SELECT TOP <quantity desired> NULL
FROM T3;
ALTER TABLE Numbers
DROP COLUMN T;
However, if you really want to see the fastest method, here is the winner (according to my tests):
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
DECLARE @RowsToCreate int = <quantity desired>;
--Code from a post by Itzik Ben-Gan
WITH
L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY C) AS Number FROM L5)
INSERT INTO Numbers (Number)
SELECT Number
FROM Tally
WHERE Number <= @RowsToCreate;
If you want to see more methods please check out this link: What is the best way to create and populate a numbers table?