Tally tables

I first learned about tally tables from the forums at SqlServerCentral which is great resource for information about SQL Server. (Free registration is required to view the content.)

A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:

SELECT TOP 11000
 IDENTITY(int,1,1) AS N
INTO
 dbo.Tally
FROM
 Master.dbo.SysColumns sc1,
 Master.dbo.SysColumns sc2

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO

The benefit of a tally table is that it can be used to implement set-based solutions, thus avoiding cursors or WHILE loops.

Example 1: Let's say that you need to generate a list of all dates from 05/12/2008 to 06/02/2008 inclusive. You might use a temp table or table variable, and construct a loop that inserts records for each date in the range. By using a tally table, all you need is this (using variables to make it flexible):

DECLARE
 @start datetime,
 @end datetime

SELECT
 @start = '05/12/2008',
 @end = '06/02/2008'

SELECT
 DATEADD(d, (N - 1), @start)
FROM
 Tally
WHERE
 N BETWEEN 1 AND DATEDIFF(d, @start, @end) + 1

Example 2: You have an Orders table like this:

ID int IDENTITY (1,1,) NOT NULL,
ProductID int,
OrderDate datetime,
Quantity int

and you need the total orders for one product, for one year, by month. The basic query might look like this:

SELECT
 MONTH(o.OrderDate) AS OrderMonth,
 SUM(o.Quantity) AS Total
FROM
 Orders o
WHERE
 YEAR(o.OrderDate) = 2007
 AND ProductID = 49
GROUP BY
 MONTH(o.OrderDate)
ORDER BY
 MONTH(o.OrderDate)

However, this will not return a row for any month that did not have
an order. You can use a tally table to ensure that you have
totals for all 12 months, even for months without an order:

SELECT
 T.N AS OrderMonth,
 SUM
 (CASE
  WHEN MONTH(o.OrderDate) = T.N
  THEN o.Quantity
  ELSE 0 END
 ) AS Total
FROM
 Orders o
 CROSS JOIN Tally T
WHERE
 YEAR(o.OrderDate) = 2007
 AND T.N BETWEEN 1 AND 12
 AND ProductID = 49
GROUP BY
 T.N
ORDER BY
 T.N

There is one more example that I'd like to share, where you can
insert multiple rows from a single edit record, but it is too long
to post in the blog format, so I will assemble this post into
a Word document and add the URL when complete.

EDIT: The full article with examples is here.