Tally tables

first published 10/24/2009 at http://wvmitchell.blogspot.com/

 

 

I first learned about tally tables from the forums at SQL Server Central 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:

 

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

GO

 

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

 

Example 3: You have an Audit table that is populated by the application, which saves the record ID, date, user, column name, and the before & after values. The Audit table has one row for each column whose value has been changed. However, you are running an update query that needs to populate the Audit table in the same manner as the application. For the purpose of this example, the audit table looks like this:

 

IF OBJECT_ID('dbo.Audit') IS NOT NULL

DROP TABLE dbo.Audit

GO

 

CREATE TABLE dbo.Audit

       (

       RecordID int,

       BeforeValue nvarchar(50),

       AfterValue nvarchar(50),

       EditField nvarchar(50),

       EditUser nvarchar(50),

       EditDate datetime

       )

GO

 

The edit data might come from a trigger, or from another portion of your T-SQL code; but for the purpose of this example I am showing a temp table to hold the data that needs to be saved:

 

SELECT

       101 AS EditID,

       '1234 Main Street' AS Address1_old,

       '1001 Central' AS Address1_new,

       'Phoenix' AS City_old,

       'Glendale' AS City_new,

       '85012' AS ZipCode_old,

       '85301' AS ZipCode_new,

       'BILL' AS EditUser,

       '10/16/2009' AS EditDate

INTO

       #edit

 

Without using a tally table, you would need to construct three separate insert statements, one for each column, like this:

 

INSERT Audit

       (

       RecordID,

       BeforeValue,

       AfterValue,

       EditField,

       EditUser,

       EditDate

)

SELECT

       e.EditID,

       e.Address1_old,

       e.Address1_new,

       'Address1',

       e.EditUser,

       e.EditDate

FROM

       #edit e

 

INSERT Audit

       (

       RecordID,

       BeforeValue,

       AfterValue,

       EditField,

       EditUser,

       EditDate

)

SELECT

       e.EditID,

       e.City_old,

       e.City_new,

       'City',

       e.EditUser,

       e.EditDate

FROM

       #edit e

 

INSERT Audit

       (

       RecordID,

       BeforeValue,

       AfterValue,

       EditField,

       EditUser,

       EditDate

)

SELECT

       e.EditID,

       e.ZipCode_old,

       e.ZipCode_new,

       'ZipCode',

       e.EditUser,

       e.EditDate

FROM

       #edit e

 

The tally table solution is more concise & requires only one insert statement:

 

INSERT Audit

       (

       RecordID,

       BeforeValue,

       AfterValue,

       EditField,

       EditUser,

       EditDate

)

SELECT

       e.EditID,

       CASE T.N

              WHEN 1 THEN e.Address1_old

              WHEN 2 THEN e.City_old

              WHEN 3 THEN e.ZipCode_old

       END,

       CASE T.N

              WHEN 1 THEN e.Address1_new

              WHEN 2 THEN e.City_new

              WHEN 3 THEN e.ZipCode_new

       END,

       CASE T.N

              WHEN 1 THEN 'Address1'

              WHEN 2 THEN 'City'

              WHEN 3 THEN 'ZipCode'

       END,

       e.EditUser,

       e.EditDate

FROM

       #edit e

       CROSS JOIN Tally T

WHERE

       T.N BETWEEN 1 AND 3

 

In both cases, the Audit table results are the same:

 

RecordID

BeforeValue

AfterValue

EditField

EditUser

EditDate

101

1234 Main Street

1001 Central

Address1

BILL

10/16/2009

101

Phoenix

Glendale

City

BILL

10/16/2009

101

85012

85301

ZipCode

BILL

10/16/2009