In a previous article, I listed the benefits of using a dedicated date table and included a customizable script which enables you to quickly create your own version. One of my readers pointed out that he uses the date datatype, rather than using the smart integer key method, when working with SQL 2008+ databases. The smart date key is recommended by the Kimball Group and others.
I performed several tests comparing the performance of Date vs. Integer datatype joins and found the Date joins to perform faster as well.
As a result, I’ve modified my script to create a Date datatype dimension have begun using it. As before, it is in two parts. The first script can be found here. Open the script, replace DateDatabase with the database name of your choice, and run it to create the function.
Once again, I need to state that I created this script from a function called F_TABLE_DATE by Michael Valentine Jones from SQLTeam and have since gotten his permission to distribute it. That function is called in the statement below to create the date table/dimension.
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'DimDate') BEGIN DROP TABLE dbo.DimDate; END SELECT DTE.Date, DTE.NextDayDate, DTE.CalendarYear, DTE.CalendarYearQuarter, DTE.CalendarYearMonth, DTE.CalendarYearDayOfYear, DTE.CalendarQuarter, DTE.CalendarMonth, DTE.CalendarDayOfYear, DTE.CalendarDayOfMonth, DTE.CalendarDayOfWeek, DTE.CalendarYearName, DTE.CalendarYearQuarterName, DTE.CalendarYearMonthName, DTE.CalendarYearMonthNameLong, DTE.CalendarQuarterName, DTE.CalendarMonthName, DTE.CalendarMonthNameLong, DTE.WeekdayName, DTE.WeekdayNameLong, DTE.CalendarStartOfYearDate, DTE.CalendarEndOfYearDate, DTE.CalendarStartOfQuarterDate, DTE.CalendarEndOfQuarterDate, DTE.CalendarStartOfMonthDate, DTE.CalendarEndOfMonthDate, DTE.QuarterSeqNo, DTE.MonthSeqNo, DTE.FiscalYearName, DTE.FiscalYearPeriod, DTE.FiscalYearDayOfYear, DTE.FiscalYearWeekName, DTE.FiscalSemester, DTE.FiscalQuarter, DTE.FiscalPeriod, DTE.FiscalDayOfYear, DTE.FiscalDayOfPeriod, DTE.FiscalWeekName, DTE.FiscalStartOfYearDate, DTE.FiscalEndOfYearDate, DTE.FiscalStartOfPeriodDate, DTE.FiscalEndOfPeriodDate, DTE.ISODate, DTE.ISOYearWeekNo, DTE.ISOWeekNo, DTE.ISODayOfWeek, DTE.ISOYearWeekName, DTE.ISOYearWeekDayOfWeekName, DTE.DateFormatYYYYMMDD, DTE.DateFormatYYYYMD, DTE.DateFormatMMDDYEAR, DTE.DateFormatMDYEAR, DTE.DateFormatMMMDYYYY, DTE.DateFormatMMMMMMMMMDYYYY, DTE.DateFormatMMDDYY, DTE.DateFormatMDYY, DTE.WorkDay, DTE.IsWorkDay into dbo.DimDate From dbo.F_TABLE_DATE ( '20000101','20351231' ) DTE order by 1 Go ALTER TABLE dbo.DimDate ADD PRIMARY KEY CLUSTERED ([Date]) WITH FILLFACTOR = 100 GO CREATE INDEX idx_Dates ON dbo.DimDate (NextDayDate) GO |
My example above generates every day through 2035, but nearly any date range can be used. As before, if you don’t want to have such a comprehensive Date Dimension or there are fields you don’t need, simply comment out items from the Select into query.
Querying against the Date Dimension is done the same way as with my previous script.
SELECT MY.date ,DTE.CalendarYear ,DTE.CalendarMonthName FROM MyTable MY INNER JOIN DateDatabase.dbo.DimDate DTE ON MY.date >= DTE.date AND MY.date < DTE.NextDayDate |
Any questions or suggestions for improvement?
[...] Calendar Tables – Why You Need One. *** Note: The code below is for use with SQL 2005 and previous versions. I have written new code to take advantage of the Date Datatype in SQL 2008 and above which can be found here. [...]