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 (
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
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.