Gullimeel (7/9/2012)
I completely disagree. If I know I want rows where the createdDate was between '2012-05-01 13:32:00.000' AND '2012-05-01 13:35:00.000' why on earth would I want to convert those to integers first? I don't believe there is any evidence that it would be faster to scan or seek based on an integer or bigint versus a datetime. Logically, it makes no sense to me and theoretically it makes no sense to me. Based on the way that SQL Server stores this information, I would prefer not to do the calculation first.
I am not trying to say that int is faster than date or like that. It is simply to save the space both on table and any index. This doesnt matter on small table but when you are talking about the 500GB tale then 4 extra bytes in a table also makes diff to the size of the table as well as index..
There will be just two calculations if at all needed. based on start and end..
Well, I see where your thought process is, but I disagree. I prefer to store my data as useful data. If I want to use that column, I have to convert back to datetime which is a waste of resources to me. It is also more work for my developers and my system. I suppose if space is a large issue, you may have no choice. However, I would certainly not jump to that without knowing that there was a severe constraint on space. Especially with the various forms of compression available.
Jared
CE - Microsoft