Technical Article

Virtual tally table function

,

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
CREATE FUNCTION [dbo].[itvfTally]
    (
     @pMin BIGINT
    ,@pMax BIGINT
    )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN

    /* Uses the Bates algorithm for generating the rows from         */    /* http://www.sqlservercentral.com/scripts/Tally+Table/99617/    */
    /*
    Usage:
    SELECT N FROM [dbo].[itvfTally](1001,1100)
    SELECT N FROM [dbo].[itvfTally](-10,100)
    SELECT N FROM [dbo].[itvfTally](0,1000)
    SELECT N FROM [dbo].[itvfTally](1,1000)
    */    
    
    WITH T1(F) AS 
    (
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1 UNION ALL
        SELECT 1
    ),
    T2(F) AS 
        (SELECT 1 FROM T1 A, T1 B),
    T3(F) AS 
        (SELECT 1 FROM T2 A, T2 B),
    T4(F) AS 
        (SELECT 1 FROM T3 A, T3 B),
    cteTally(N) AS 
        (
        SELECT TOP ((@pMax-(((ABS(@pMin)+@pMin)/2)))+1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
            + ((((ABS(@pMin)+@pMin)/2))-1)
        FROM T4
        )
SELECT 
    N
FROM 
    cteTally T
;

Rate

4 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (4)

You rated this post out of 5. Change rating