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

 

WITH

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

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

               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) *

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

     FROM sys.all_columns

     UNION SELECT 1,1)

SELECT @factorial=@factorial*f

FROM t

WHERE n%2=@n%2;

 

SELECT @n AS n, @factorial AS factorial;

 

--ab

Further Reading:

• Virtual Auxiliary Table of Numbers (Itzik Ben-Gan)
• You REQUIRE a Numbers table! (Adam Machanic)
• A Simple Formula to Calculate the ISO Week Number - SQLServerCentral (Jeff Moden)
• Creative Solutions by Using a Number Table (Greg Larsen)
• How to Create and Use A Numbers Table (msdn)

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