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.
/***********************************************************************
Purpose:
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
BEGIN
RAISERROR('RUN ABORTED. tsqlc_Tally already exists.',11,1)
RAISERROR('Please drop the table if you wish to recreate.',10,1)
RETURN
END
--===== 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
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.tsqlc_Tally TO PUBLIC
GO