SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calendar Tables


Calendar Tables

Author
Message
Victor Kirkpatrick
Victor Kirkpatrick
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2202 Visits: 452
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.
wagner crivelini
wagner crivelini
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 282
Nice article!
People tend to avoid time tables relying on SQL functions. This is a good approach if you don't need to do those calculations frequently.
I remember a project where people wanted to kill me when I designed a table with time period and correspondent details as worked hours in that period and so on.
When somebody decided to change those periods from calendar months to something different, programmers got crazy and came to me to see what to do....
Just to hear all we had to do was to run a couple of UPDATE statements in the previously infamous time table :-D
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4076 Visits: 2629
I was first introduced to calendar and time tables when I started learning about data warehousing. I have always thought they would be useful in the relational world as well. It is nice to see someone using them.
awilbournsqlcentral
awilbournsqlcentral
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 47
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.
GRScow
GRScow
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 79
Good article on a key topic for db/warehouse developers like me.

It seems that from a maintenance viewpoint, it would be much easier to define all of the variations on the main date as computed fields. Adding date formats and derived dates is as simple as adding a calculated field to the table.

We have several significant data collection and reporting systems and various data sources which use various date formats (including mainframes where records are defined with YY fields and MM fields - text and int types, combined and split). Any date fields in a data table can be joined to the date table on the appropriate date field(s) and of course we always try to store the resulting records in a Sql native datetime field.

Seems like this provides the performance benefits of joining to a table along with the super-simple maintenance - there is only one step on one field when adding months to the table.

Cheers!
katesl
katesl
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 473
>>
Here we've always been of the belief that you shouldn't have tables with date ranges, because tables get created and populated, the query in question works, and the table gets forgotten about. You come into work on Jan 2nd, and something has stopped working, because there are no valid dates in the table, and the query that relies on this has failed because no-one updated the table, or the people who know about the table have left.

I understand about the performance benefit, and it makes sense. Perhaps there could be a recurring job that runs every Dec XXth, that adds to the contents of that table so that the table always holds at least a years extra data, for instance. That way, the table will never run out of dates.
>>

So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?

_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Tom Williams-175034
Tom Williams-175034
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 37
katesl (7/15/2010)
>>

So it's OK to depend on a job that runs infrequently, but it's not OK to depend on a table that has to be maintained infrequently? Where's your DBA?


No, neither are OK, but I believe the former might just be safer than the latter. If the DBA leaves, and doesn't tell his replacement about this magic table of numbers, do we wait for the new DBA to find this table, and work out why it's there?

I think you're marginally *less* likely to have a well tested job screw up, than have a DBA remember to infrequently remember to maintain that table.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 1499
Nice article. Thanks for the time and effort that you took to write it.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

rchantler
rchantler
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 582
We advocate and use the calendar table approach too. We deal with reams (tech term) of daily financial data and this approach makes many difficult things easier and some impossible things possible ;-)

If not for the calendar table, we'd be recomputing date ranges, etc. to the nth degree. With the table, they're computed once (and referenced many times). It helps with the load.

Thanks for the article.
scott.pletcher
scott.pletcher
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 473
I don't object to a calendar table per se, and a specific calendar table does have certain advantages over a tally table.

But I'm not sure that big of a performance difference should necessarily be one of them.

When using the tally table, I don't think it's necessary to use functions on the table column in the JOIN clause. That will automatically limit the usefulness of any index on that column.

Couldn't we do something like this instead?:


...
FROM Tally
LEFT JOIN #Stay
ON
N <= 12 AND
ArrivalDate >= @StartYear1 AND
(ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear1) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear1)) OR
ArrivalDate BETWEEN DATEADD(MM, N - 1, @StartYear2) AND DATEADD(MS, -3, DATEADD(MM, N, @StartYear2)))

GROUP BY ...




I.e., put all the JOIN criteria in the JOIN and make sure we don't use functions on the ArrivalDate. I'm not sure the optimizer is sophisticated enough to recognize that it should be able to use the index here, but I think at least it gives it a chance :-) .

I know for a single month I usually an index seek vs. a scan using this style of coding, but for the side-by-side month, I'm not sure.

Scott Pletcher, SQL Server MVP 2008-2010
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search