Tally tables

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

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

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

CREATE TABLE dbo.Audit

(

RecordID int,

BeforeValue nvarchar(50),

AfterValue nvarchar(50),

EditField nvarchar(50),

EditUser nvarchar(50),

EditDate datetime

)

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