Calendar Tables

IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL
DROP TABLE dbo.Tally;
GO

DECLARE @StartDate DATETIME = '18501231';

SELECT Tab.Number,
@StartDate + Tab.Number AS Date,
MONTH(@StartDate + Tab.Number) AS Month,
YEAR(@StartDate + Tab.Number) AS Year
INTO dbo.Tally
FROM (SELECT TOP(100000)
ROW_NUMBER() OVER (ORDER BY C1.column_id)
FROM model.sys.columns AS C1
CROSS JOIN model.sys.columns AS C2
CROSS JOIN model.sys.columns AS C3) AS Tab(Number);

ALTER TABLE dbo.Tally
ALTER COLUMN Number INT NOT NULL;
GO

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED(Number)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Date ON dbo.Tally(Date)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Year ON dbo.Tally(Year)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 100);
GO

EXEC sp_spaceused 'dbo.Tally';
-- Tally 100000 8512 KB 3328 KB 4432 KB 752 KB

codebyo (2/25/2012)

Excellent article! Thank you.

Now I have a question for you more experienced guys and gals. BigGrin

If I use a tally table like the one below would that be alright? I'm
trying to use a single tally table for numbers and dates as well.

IF OBJECT_ID('dbo.Tally', 'U') IS NOT NULL
	DROP TABLE dbo.Tally;
GO

DECLARE @StartDate DATETIME = '18501231';

SELECT Tab.Number,
       @StartDate + Tab.Number AS Date,
       MONTH(@StartDate + Tab.Number) AS Month,
       YEAR(@StartDate + Tab.Number) AS Year
  INTO dbo.Tally
  FROM (SELECT TOP(100000)
               ROW_NUMBER() OVER (ORDER BY C1.column_id)
          FROM model.sys.columns      AS C1
         CROSS JOIN model.sys.columns AS C2
         CROSS JOIN model.sys.columns AS C3) AS Tab(Number);
         
ALTER TABLE dbo.Tally
	ALTER COLUMN Number INT NOT NULL;
GO

ALTER TABLE dbo.Tally
	ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED(Number) 
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
	FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Date ON dbo.Tally(Date)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
	FILLFACTOR = 100);
GO

CREATE NONCLUSTERED INDEX IX_Tally_Year ON dbo.Tally(Year)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
	FILLFACTOR = 100);
GO

EXEC sp_spaceused 'dbo.Tally';
-- Tally	100000     	8512 KB	3328 KB	4432 KB	752 KB

Or should I just remove the Number column and calculate the ROW_NUMBER everytime I use the table? I'm trying to think performance-wise. Have a great weekend y'all!

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
Thousands (N) AS (SELECT t1.N FROM Tens t1, Tens t2, Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1, Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions),
Months (N, MonthStart) AS (SELECT TOP (25*12)
N, DATEADD(MONTH, N, '1999-12-01T00:00:00')
FROM Tally)
SELECT N,
MonthStart,
NextMonth = DATEADD(MONTH, 1, MonthStart),
MonthDescr = CONVERT(CHAR(3), caMonth.MonthName) + '-' +
RIGHT(CONVERT(CHAR(4), YEAR(MonthStart)),2),
caMonth.MonthName,
YearMonth = (YEAR(MonthStart)*100) + caMonth.MonthNbr,
caMonth.MonthNbr
FROM Months
CROSS APPLY (SELECT MonthNbr = DATEPART(MONTH, Months.MonthStart),
MonthName = DATENAME(MONTH, Months.MonthStart)) caMonth
ORDER BY Months.MonthStart;