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)

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: 2099 | Views in the last 30 days: 1
Related Articles

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


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


Virtual tally table function

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