The Sins of Old: Time_T When building a cube or other reporting
mechanism, quite often it is nessecary to be able to produce a
definitive list of times. One of the classic 'Sins of SQL' is to
create a table to house time values, with up to 3,600 rows for a
single hour if you go as low as the seconds values in your data.
The trouble is that then each fact table joins to your time
table along the lines of: This pattern effectively creates a
surrogate identity for time itself, and has some nasty
consequences for SQL developers: - Adding records to the Fact
table requires a lookup on the time (or an insert if it's a new
time). - The SQL to query this becomes complex and unwieldy. -
Assuming a you are querying an arbitrary time range of say, 45
minutes in the table, if you work at the seconds resolution your
time table will have 2,700 keys that match, and each of these
keys needs to then be linked back to the table. This decrease in
performance is less if you only go down to the minutes or even
hours, but is still