RE: Calendar table – SQLServerCentral

SSC Guru

Points: 76944

January 30, 2018 at 12:16 pm

#1977932

K. Brian Kelley - Tuesday, January 30, 2018 11:40 AM
drew.allen - Tuesday, January 30, 2018 11:37 AM
K. Brian Kelley - Tuesday, January 30, 2018 11:21 AM
Another idea for a PK is a composite key (two columns) consisting of the year + the day of the year. There are some uses for this.
I would just use the date itself. It's only a single field instead of being a composite key. It's more human readable. I could see having an index on year/day of the year, but I would not make it the PK.
Drew
PK doesn't have to be human readable. And there's value to determining what happened on the Nth of the year over multiple years. Think about how you'd have to deconstruct the key, for instance, if you wanted to compare temperature highs over the last 40 years for the same day. Yes, you could look at month and day columns, but it's easier if you have a single column, excepting leap years, of course. But that throws anything of this sort off. Therefore, it really depends on what you're trying to do here. 🙂

No, the PK doesn't have to be human readable, but it certainly doesn't hurt. If you don't care about human readability, use an INT identity instead. Again it's going to be smaller and only a single field.

But is there enough value in "determining what happened on the Nth of the year over multiple years" to justify having it be the PK instead of a regular index? I've been working with SQL server almost 20 years and I've never once written a single query where I needed to use the Nth day of the year. I've used the end of the month, the end of the year, the end of the fiscal year, the end of the pay period, relative date ranges, and absolute date ranges, but never have I needed to use the Nth day of the year. Perhaps this is simply a reflection of the industries I've worked in and it might be more common in, for example, a weather database, but I haven't seen it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA