Tuesday, April 9, 2013

The Tally Table (and a nifty factorial function)

I have not posted anything since September and with good reason. Last year to promote this blog, I began participating in some online SQL forums and, long story short, realized that I needed to get on board with set-based SQL development. I always thought my code was slick until I did a deep dive into this whole “set-based thing.” Since then I have seen time and time again that the set-based solution almost always dramatically outperforms its iterative counter-part, often with cleaner, more elegant code.

Which brings me to The Tally Table. I’ve also heard it referred to as a “numbers table” or “auxiliary table of numbers”. I call it a tally table to give credit where credit is due. What you call it is not important, getting rid of those loops and cursors is. Nothing has helped me do that more than this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop

In future posts you will see many tricks with the tally table. For now, here's a nifty little factorial script.

Set-Based Factorial Query:

DECLARE @n int=5,          -- @n is the number to aggregate

        @factorial bigint=1; -- set @factorial to 1



t(n,f) AS (SELECT TOP(@n)

               ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1,


              (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1)

     FROM sys.all_columns

     UNION SELECT 1,1)

SELECT @factorial=@factorial*f


WHERE n%2=@n%2;


SELECT @n AS n, @factorial AS factorial;



The new xmlsqlninja logo ('X-M-L'-'See-Quel' 'ninja')

Further Reading:

Last Updated: 4/19/2013 (code cleanup)


  1. Nice job and thanks for the referral, Alan. Was good to meet you in person, as well.

  2. Hi Alan,

    I'm not sure what I was doing to run across this post again, but I missed a simplification the first time around. Here's the simplification..

    DECLARE @N INT ,@Factorial BIGINT;
    SELECT @N = 5 ,@Factorial = 1;

    @Factorial = @Factorial * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns;

    SELECT N= @N, Factorial = @Factorial;

  3. Hi Jeff!

    Thanks for stopping by and sorry for the late reply; I've been working around the clock on a project deadline of today (4/1). Your solution is excellent! Very well done (as always).

    BTW. It looks like a few co-workers and I will be heading to Detroit to learn about a Partitioning System for "WORM" Tables. ;)