What is the advantage of using a date size table over directly storing a date?

Question

I need to keep a fairly large data history. I studied the best ways to store such an archive. It seems that a data-based approach is what I need to solve. It is recommended that you use the date size chart, not the date itself. Can someone explain to me why it would be better to have a separate table? I do not need to summarize any data, just quickly and efficiently access it any day in the past. I'm sure something is missing, but I just don’t see how to store dates in a separate table is better than just storing the date in my archive.

I found these enlightening posts, but nothing that completely answers my question.

• What should I keep in mind when creating an OLAP solution from scratch?
• Date table and query dimension and indexes
• What is the best way to store historical data in SQL Server 2005/2008?
• How to create a history fact table?

Answer 1

Well, one advantage is that as a dimension you can store many other date attributes in this other table - it's a holiday, it's a weekday, what fiscal quarter it is in, what is the UTC offset for (or several) time zones etc. etc. Some of them can be calculated at runtime, but in many cases it is better (or only possible) for a preliminary calculation.

, DATE , (NULL), , 1900-01-01, ( , ), 1899-12-31 (, , ..). , , , DATE /, - "" .

DATE, INT (!), , .. , , , . - .

Answer 2

, . , , , . , 365 , . , .