Victor Kirkpatrick (7/15/2010)
The key isn't random just because it's an identity, as long as when the records for the date table were created in the correct "month" order. And no, it isn't dangerous.Nice article. I already have a numbers table that I use regularly, maybe it's time for a dates table like this.
As for for Tom's comments concerning staying away from tables like this where you are worried about not have data for a given year. Relax. You could load this table through 2500 and it would still easily reside in memory.
As a BI developer that is dealing with a Date Dimension that used such a means to create the key, it is a royal pain when you are trying to sift through millions of rows of data and where you are looking for a certain date range. Rather than having to go look up what value 5234 is, it would be nice to have the value be the date. This also helps developers if they want certain data and know the date, but only need to query on the date itself, so no need to join the extra table to do so, only need it if you need more dimension data. So a date table would be like 20100715 for today, something very easy for application developer to format and pass along.
Anyhow, I have been on the dev and DBA side and find that if you can help make your normalized data make a little since (with dates at least) it helps make finding issues in the data easier, because is it the app or the data is always the question.