Click here to monitor SSC
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...

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"

Total article views: 6329 | Views in the last 30 days: 10
 
Related Articles
SCRIPT

Virtual tally table function

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

ARTICLE

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

FORUM

Performance and handling differences for tally function across servers

string cleaning function using tally table loop handled differently

FORUM

MAXIMUM instance

MAXIMUM instance

BLOG

Tally Table - String Cleaning

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

Tags
advanced sql    
 
Contribute