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 |