Date Table/Dimension Querying and Indexes

Question

I'm creating a robust date table want to know the best way to link to it. The Primary Key Clustered Index will be on the smart date integer key (per Kimball spec) with a name of DateID. Until now I have been running queries against it like so:

select Foo.orderdate -- a bunch of fields from Foo
      ,DTE.FiscalYearName
      ,DTE.FiscalPeriod
      ,DTE.FiscalYearPeriod
      ,DTE.FiscalYearWeekName
      ,DTE.FiscalWeekName  
      FROM SomeTable Foo
     INNER JOIN
       DateDatabase.dbo.MyDateTable DTE
     ON DTE.date = CAST(FLOOR(CAST(Foo.forderdate AS FLOAT)) AS DATETIME)

Keep in mind that Date is a nonclustered index field with values such as: 2000-01-01 00:00:00.000

It just occured to me that since I have a clustered integer index (DATEID) that perhaps I should be converting the datetime in my database field to match it and linking based upon that field.

What do you folks think?

Also, depending on your first answer, if I am typically pulling those fields from the date table, what kind of index how can I optimize the retrieval of those fields? Covering index?

Answer 1

Even without changing the database structure, you'd get much better performance using a date range join like this:

select Foo.orderdate -- a bunch of fields from Foo 
  ,DTE.FiscalYearName 
  ,DTE.FiscalPeriod 
  ,DTE.FiscalYearPeriod 
  ,DTE.FiscalYearWeekName 
  ,DTE.FiscalWeekName   
  FROM SomeTable Foo 
 INNER JOIN 
   DateDatabase.dbo.MyDateTable DTE 
 ON Foo.forderdate >= DTE.date AND Foo.forderdate < DATEADD(dd, 1, DTE.date)

However, if you can change it so that your Foo table includes a DateID field then, yes, you'd get the best performance by joining with that instead of any converted date value or date range.

If you change it to join on DateID and DateID is the first column of the clustered index of the MyDateTable then it's already covering (a clustered index always includes all other fields).