Create a Tally or Numbers Table

,

To learn more about what a "Tally" or "Numbers" table is, how it works, and how it can be used to replace certain WHILE loops and other forms of RBAR, please see the article at the following link...

http://www.sqlservercentral.com/articles/T-SQL/62867/

"Tally" or "Numbers" tables are nothing more than a single column table containing a sequence of very well indexed numbers starting at 0 or 1 and incrementing in steps of 1 up to a predetermined maximum number. That maximum number should be determined by the worst case scenario expected for whatever a loop could be used for.

I normally choose the number 11,000 because it's larger than the maximum 8,000 characters for VARCHAR splits and because there are almost 11,000 days in 30 years.

As previously stated, "Tally" or "Numbers" tables are used to replace loops via a cross join in such things as CSV Splitter functions, missing date comparisons, and a wealth of other string, date, and digital functions. For more information about such functions, do a search on "Tally Table" on this site.

--Jeff Moden


"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

Rate

4.07 (15)

Share

Share

Rate

4.07 (15)