Tally Table – Number Table

A Tally Table (also known as Number Table) is an essential tool that every database administrator like to have in their databases. Such a table helps to solve a number of TSQL problems in a SET based manner. Jeff Moden provided a very interesting script which not only gives you a 1 million row tally table, but also demonstrates how to write good TSQL code.

 Create a standared "Numbers" table for use in solving T-SQL Challenges.
 Programmers Notes:  
 1. This table contains all INTEGER values from 0 (zero)  to 1,000,000 
    inclusive and has a clustered index on the single INTEGER column 
    called "N".

 2. The table is named for the challenges so as not to overwrite 
    existing Tally or Numbers tables by accident.  Even then, if the 
    tsqlc_Tally exists, this run will abort.

 3. This table is created using a cross-join of the 
    master.sys.all_columns available in SQL Server 2005.  If you need
    to use this script in SQL Server 2000, please change that table name
    to master.dbo.syscolumns in both places.

 4. An IDENTITY function was used to build the values instead of 
    ROW_NUMBER() just in case someone does need to run the code in an 
    SQL Server 2000 environment.

 5. The code takes approximately 6 seconds or less to execute on most
    properly configured servers, desktops, and laptops.

 6. Including the clustered index, the table occupies approximately
    13MB according to sp_SpaceUsed.

 Revision History:
 Rev 00 - 14 Dec 2009 - Site contributors - Initial release.
--===== If the table already exists, something may be wrong and we need 
     -- to alert the operator before continuing.
     IF OBJECT_ID('dbo.tsqlc_Tally','U') IS NOT NULL
        RAISERROR('RUN ABORTED. tsqlc_Tally already exists.',11,1)
        RAISERROR('Please drop the table if you wish to recreate.',10,1)

--===== Create and populate the Numbers table on the fly.
     -- The IDENTITY makes "N" a NOT NULL column for use as a PK.
 SELECT TOP 1000001
        IDENTITY(INT,0,1) AS N
   INTO dbo.tsqlc_Tally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2

--===== Add a tightly packed clustered Primary Key to maximize 
     -- performance and minimize space used.
  ALTER TABLE dbo.tsqlc_Tally
    ADD CONSTRAINT PK_tsqlc_Tally_N 

--===== Allow the general public to use it