Tally Table

I have seen others post a similar method of creating a Tally Table that was attributed to Itzik Ben-Gan. The posts that I have seen didn't explain how it worked or how to modify the query to adjust the range of numbers returned.

1. The first table T1 with the UNIONs creates a table with 10 rows.  
2. This table is CROSS JOINed with itself and creates a second table T2 with 10*10 or 100 rows.
3. This process is repeated by CROSS JOINing T2 with itself to get table T3 containing 10,000 rows.
4. One more CROSS JOIN or T3 will create table T4 containing 100,000,000 rows.

Note: If 10,000 rows or less is needed then table T4 does not have to be created, and the final select statement can select from T3 instead.

This results in a table with one field and 100 million rows with each field containing the number 1.  The actual numbers are provide by the ROW_NUMBER() function.  

To get the ROW_NUMBER() function to evaluate an OVER clause is required.  ORDER BY (SELECT NULL) satifies the conditions of the OVER clause and provides the incrementing numbers in an unnamed field.

The TOP clause is used to limit the returned number of rows.  If TOP (1000) is used then a table with 1,000 rows contaning one field with numbers ranging 1-1,000 is created.

Note: If the starting number in the sequence need to be some number other than 1, then an offset can be used in the final SELECT statement to shift the number range in a positive or negative direction.  For Example:

• To generate 1,000 rows with a range of 0 - 999 use

 SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM T4;

• To generate 100 rows with a range of 1,000 to 1,099 and give the field a name of 'CheckNumber' use

SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +999 AS CheckNumber FROM T4;