codebyo (2/25/2012)

---

Excellent article! Thank you.

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

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';

-- Tally100000 8512 KB3328 KB4432 KB752 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!