SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Create a Tally or Numbers Table

By Jeff Moden,

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...


"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"

Total article views: 6408 | Views in the last 30 days: 8
Related Articles

Virtual tally table function

An inline table-valued function (iTVF) that will produce a virtual tally table on-the-fly. The funct...


The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Many people have used a "Numbers" or "Tally" table without really knowing what it does. This is an i...


Tally Generator

Inline function generating the set of numbers based on submitted parameters.


Performance and handling differences for tally function across servers

string cleaning function using tally table loop handled differently


MAXIMUM instance

MAXIMUM instance

advanced sql