Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 6210 | Views in the last 30 days: 35
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...


Performance and handling differences for tally function across servers

string cleaning function using tally table loop handled differently


MAXIMUM instance

MAXIMUM instance


Tally Table - String Cleaning

Overview This is something that most people eventually need for reporting purposes.  This functio...

advanced sql    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones