Often times in SQL Server, it is necessary to display ranges of information. Whether it is a listing of all dates in a range, or simply a list of numbers, the foundation can be a numbers table. This article will concentrate on what a numbers table is and the different methods of generating the table.

What is a numbers table?

A numbers table is a table containing sequential integer values. The size of your table will vary depending upon how you may choose to use it. Most practical applications that I have encountered have never called for more than 100,000 records but we will be creating ours with 1,000,000 records just to be safe. Remember that the larger you choose to create your numbers table, the more space it will take up and the longer it will take to generate.

The Structure

The structure of a numbers table is very simple. It must only contain one column and it should be the clustered index. For performance reasons, the table is created, populated, then the clustered index is applied.

create table util_nums
     (n     int notnull
     )
 go
 alter table util_nums add constraint pk_util_nums primary key clustered(n)with fillfactor =100

I choose the naming convention of util_ for all of my utility tables, but if you or your organization have a different convention, that is OK as well.

Populating the table:

There are many way to populate this table.

You can write a while loop to increment a counter and insert 1 record at a time: (about 20 minutes) *1

declare @n int
 set @n=1
 while @n < 1000000
 begin
     insert into util_nums (n) values(@n)
     set @n=@n + 1
 end

There is another way that this can be done which is much faster: (about 14 seconds) *1

 declare @n int
 set @n=1
 begin tran
 while @n < 1000000
 begin
     insert into util_nums (n) values(@n)
     set @n=@n + 1
 end
 commit

The key difference between the above two methods is the transaction. Putting a begin tran/commit around the insert statement causes SQL Server to hold the information in the transaction log until it is either committed, or rolled back.

You can use a while loop to insert batches in a doubling method: (about 5 seconds)*1

 declare @n int
 set @n=1
 begin tran
 insert into util_nums(n)values(@n)
 while @n < 1000000
 begin
     insert into util_nums (n)
     select @n+n from util_nums
     set @n=@@Rowcount*2
 end
 commit

You can use a while loop to insert batches of X number: (about 10 seconds) *1

 declare @n int
 set @n=1
 while @n < 1000000
 begin
     insert into util_nums
     select * from vw_nums
     where n between @n and @n + 99999
 set @n=@n + 100000
 end

You can use a Common Table Expression to generate the numbers: (about 8 seconds)*1,2

;with   cte0 as (select 1 as c union allselect 1),
       cte1 as (select 1 as c from cte0 a, cte0 b),
       cte2 as (select 1 as c from cte1 a, cte1 b),
       cte3 as (select 1 as c from cte2 a, cte2 b),
       cte4 as (select 1 as c from cte3 a, cte3 b),
       cte5 as (select 1 as c from cte4 a, cte4 b),
       nums as (select row_number() over (orderby c) as n from cte5)
 insert into util_nums(n)
       select n from nums
       where n <= 1000000

This statement is basically a giant cross join statement. With each extra common table expression, you are joining all current records to all previous records. I would probably use the CTE method, since it will return 4 billion + integers if you need it to.

Size of the table

When it is all said and done, this table of 1 million integers will take about 13MB of space in your database. And with the ever decreasing cost of disk capacity, I believe the benefits of such a table far outweigh the extra space.

What's Next?

The next topic I will cover is putting this new table to good use by using it to generate a time coordinate table.

If you would like any additional information about this or to request a future topic, please feel free to email me at Brandon@Galderisi.com.

*1 – All times may vary. All times taken on extrapolation of 100K sample.
*2 – The source of this CTE is unknown. It was found online and provided to me by a co-worker.

Test Machine:
XEON 3060
4GB Ram (500MB allocated to SQL)
SQL Server 2005 Express Edition