TSQL DateTime to DateKey Int

Question

In Scaling Up Your Data Warehouse with SQL Server 2008 R2, the author recommends using an integer date key in the format of YYYYMMDD as a clustered index on your fact tables to help optimize query speed.

What is the best way to convert your key date field to the Date Key? I feel the following would work, but is a bit sloppy:

select Replace(CONVERT(varchar,GETDATE(),102),'.','')

Clearly, I'm not using getdate, but rather a date column in the table that will be using in my aggregations.

First, how would you suggest making this conversion? Is my idea acceptable?

Second, has anyone had much success using the Date Key as a clustered index?

Solution 1

ISO long (112) would do the trick:

SELECT CONVERT(INT, CONVERT(VARCHAR(8), GETDATE(), 112))

Casting getdate() straight to int with ISO 112 gives 41008 for some reason, but going via a VARCHAR seems to work - i'll update if i think of a faster cast.

EDIT: In regards to the int only vs varchar debate, here are my findings (repeatable on my test rig & production server) Varchar method uses less cpu time for half a million casts but a fraction slower overall - negligible unless your dealing with billions of rows

EDIT 2: Revised test case to clear cache and differnt dates

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  YEAR([Date]) * 10000 + MONTH([Date]) * 100 + DAY([Date])
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 255ms.    
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
WITH    RawDates ( [Date] )
          AS ( SELECT TOP 500000
                        DATEADD(DAY, N, GETDATE())
               FROM     TALLY
             )
    SELECT  CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
    FROM    RawDates
SET STATISTICS TIME OFF 

(500000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 602ms

Solution 2

Instead of creating a DateKey using the YYYYMMDD format, you could use the DATEDIFF function to get the number of days between 0 (i.e. "the date represented by 0") and the date you're making the DateKey for.

SELECT DATEDIFF(day,0,GETDATE())

The drawback is that you can't easily look at the value and determine the date, but you can use the DATEADD function to calculate the original date (I've also seen this trick used truncate the time part of a datetime).

SELECT DATEADD(day, 41007, 0)

(Note: 41007 is the result of the DATEDIFF function above when I ran it on 4/10/2012.)

Solution 3

Converting to strings and back again can be surprisingly slow. Instead, you could deal entirely with integers, like this:

Select Year(GetDate()) * 10000 + Month(GetDate()) * 100 + Day(GetDate()) 

In my brief testing, this is slightly faster than converting to string and then to int. The Year, Month and Day function each returns an integer, so the performance is slightly better.