Generating an Integers Table

One of the first jobs I do when setting up any new database is to create a tool set of useful objects. And an integers table is normally the first of these – having a source of numbered rows is often handy for generating test data and to use in cross joins to add additional rows to a query. Generating a permanent table of values will save generating these on the fly whenever they are required.

create table dbo.integers
(row_num int primary key clustered)

 

In a new DB there will normally be a few rows in sys.all_columns and this can be cross joined one or more times to get the required number of rows and applying a top predicate to get the number of rows required, 1M will suffice for most needs.

insert 
into	dbo.integers
select	top 1000000
	ROW_NUMBER() over (order by c1.object_id) 
from	sys.all_columns c1
	cross join sys.all_columns c2

 

Since a lot of uses of the integers table will only involve a few rows there’s a case for also generating a smaller version as well for the (albeit minimal) performance benefit.