Is there any performance advantage to storing dates as ints in MS SQL Server

Question

Either in terms of space or speed of queries and comparisons of fields?

Answer 1

Datetime columns are already stored in SQL Server as 8 byte binary values- 4 bytes for the date part and 4 for the time part. Any other representation you see is just a view to make them more human readable.

If you know you don't need the full range of values (say you don't care about the time part and aren't using 2008, for example), you might be able to do a little better with an integer. But I really doubt you'd notice any performance difference on all but the largest and busiest tables.

As always, you need to profile to be sure.

Answer 2

with ints you also need to have code to make sure it is really a date instead of 20090230 being inserted If you are on SQl server 2008 you can use the DATE datatype which only uses 3 bytes

Answer 3

MS SQL 2008 can store and has column type date and time separately. Why bother using int.

Answer 4

Storing dates as ints would be a bad thing because you would always have to recreate the data as a date in order to do date math or date comparisons. This would negatively impact performance. Further, storing dates as ints would require writing a trigger to ensure no non-dates are ever added to the field. If you don't do this, you will have date integrity issues where there are invalid dates such as 2302009 stored in your field.