4

I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server.

We know we want daily partitions. The data has a natural DATETIME field in it, so my gut instinct is to partition on that value. However, we have discussed the possibility of adding a DATE column (with just the time information stripped out), or possibly coding the date as an integer (YYYYMMDD, e.g., 20130930) and using that as the partitioning column.

All queries on this data will be on a specific date (WHERE ItemTime = '2013-09-30') or range of dates (WHERE ItemTime BETWEEN '2013-09-15' AND '2013-09-30'). For now, we always query on a date, but future requirements may include time details (WHERE ItemTime BETWEEN '2013-09-29 20:30:00' AND '2013-09-30 10:15:45').

I have tested performance of each strategy on a few hundred thousand rows of data, and seen no real difference. The production deployment, however, will be in the hundreds of millions up to maybe a couple billion rows.

Is one of these strategies going to lead to more efficient queries than the others? Why or why not?

Thanks for your help.

[EDIT] The queries will be formatted by application code, so I'm not concerned about how to translate between DATETIME, DATE, and INT. I can assume the query will be properly formatted based on whichever partitioning scheme is chosen. I'm just trying to find out if partition elimination will be faster using one of these data types.

Improve this question
3

You shouldn't get any additional performance gain by adding the extra column. If any, I doubt it will outweigh the additional storage cost required for the extra column.

There is one perk that you'll get from having a date only column and that is that you can have a date dimension. If this is for a data warehouse, I'd definitely recommend including.

If you decide to add the additional column (and assuming you're on SQL 2008 +), use the Date datatype. Int (formatted in YYYYMMDD) used to be the recommended format for partitioning, as it was cheaper (@ 4 bytes/row) than datetime (@ 8 bytes/row). Date is 3 bytes/row and is in a natural date format. Also, INT is a pain in the butt to query on as you have to add conversions in your' search arguments:

WHERE DateID = (CONVERT([int],CONVERT([char](8),getdate(),(112)),(0)))
Improve this answer
5
  • 1
    I always advocate DATE too, but the DW purists like INT for a few reasons (I'm not crazy about them) - they allow you to foreign key to invalid / missing dates without using NULL. I definitely agree with you that DATE is the right choice (though you haven't really addressed how to satisfy queries on ranges that include time). – Aaron Bertrand Sep 30 '13 at 21:59
  • For the invalid/missing dates you can just as easily use an irrational value like 0001-01-01 as with int (would probably use 0 or -1 for that). – brian Sep 30 '13 at 22:54
  • As for the foreign key, there's really no need for foreign keys on datetimes. The partition function will figure out which HOBT the data should go in. – brian Sep 30 '13 at 22:56
  • Tell that to purists (who don't want nonsense / token dates either, since you still can't tell what 0001-01-01 means compared to 0001-01-02 if there are different reasons for a missing date). As for the foreign key, think about many fact tables with dates that look up to a central date table with not just the date but a bunch of pre-calculated information like IsWeekday, IsHoliday, FiscalQuarter, etc. - things that are calculated across many queries and are more efficient / less complicated to store once. It may not be your choice but it's a valid design that can't be discarded. – Aaron Bertrand Sep 30 '13 at 23:59
  • Agreed, I included the bit about the Date dimension. – brian Oct 1 '13 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.