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)

Virtual tally table function

By Steven Willis,

This iTVF will create a tally table of any size with user-defined lower and upper bounds. So if you want a tally table with 100,000 rows that starts at 250,001 and ends at 350,000, just enter the lower and upper bounds as parameters and the function does the rest. As written, it converts any negative number entered as a min parameter into zero. (A max parameter < 1 will generate an error.)
I ran a performance test on this function against a "physical" tally table with 10,000,000 rows using Jeff Moden's test methodology and test data generator. The testing code can be made available on request. This function performs as well or better than the physical tally table.
So if you have an application that requires an upper bound greater than the traditional 10-11,000 rows of the common tally table, or if you need to set a range of row numbers, this function will do that easily with no loss of efficiency.
I want to give credit to Jeff Moden for generating the concept through his articles and research on tally tables; credit to Dwain.c for his keen-eyed suggestions and tweaks; and of course to Brian Bates and his row-generating algorithm without which this function could not have evolved.
Steven Willis

Total article views: 2012 | Views in the last 30 days: 11
Related Articles

Performance and handling differences for tally function across servers

string cleaning function using tally table loop handled differently


Barcodes Code128 generator function

Searching for a barcode Code128 generator function


Tally Table - String Cleaning

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


Barcode 128 generator (using scalar function)

Function to generate barcodes standard code 128


Virtual tally table function

Comments posted to this topic are about the item [B]Virtual tally table function[/B] While developin...


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