Often the use of an auxiliary table can assist you to write more efficient code than a solution that doesn’t. Auxiliary tables are great at solving problems that you may be tempted to use a cursor to solve in the place of a set based solution. The following example shows how to generate an auxiliary numstable and then use this table to generate a calendar table. This task is one that is often performed as a row-by-row operation in a cursor as apposed to using a set based solution.
— ~~~ START Generate Auxiliary Table ~~~
CREATE TABLE dbo.Digits (i INTEGER NOT NULL PRIMARY KEY)
INSERT dbo.Digits SELECT 0
INSERT dbo.Digits SELECT 1
INSERT dbo.Digits SELECT 2
INSERT dbo.Digits SELECT 3
INSERT dbo.Digits SELECT 4
INSERT dbo.Digits SELECT 5
INSERT dbo.Digits SELECT 6
INSERT dbo.Digits SELECT 7
INSERT dbo.Digits SELECT 8
INSERT dbo.Digits SELECT 9
CREATE TABLE dbo.Nums (seq INTEGER NOT NULL PRIMARY KEY)
INSERT INTO dbo.Nums
SELECT (10000 * D5.i) + (1000 * D4.i) + (100 * D3.i) + (10 * D2.i) + D1.i
FROM Digits AS D1, Digits AS D2, Digits AS D3, Digits AS D4, Digits AS D5
WHERE ((10000 * D5.i) + (1000 * D4.i) + (100 * D3.i) + (10 * D2.i) + D1.i) > 0
— ~~~ END Generate Auxiliary Table ~~~
CREATE TABLE dbo.Calendar (dt DATETIME PRIMARY KEY)
INSERT INTO dbo.Calendar
SELECT DATEADD(DAY, seq, ‘20051231’)
FROM dbo.Nums
WHERE DATEADD(DAY, seq, ‘20051231’) <= ‘20061231’
