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

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: 5735 | Views in the last 30 days: 44
 
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

BLOG

Tally Table - String Cleaning

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

FORUM

MAXIMUM instance

MAXIMUM instance

Tags
advanced sql    
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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