Sql 2005, is it faster to sort by datetime or by int ?

Question

If sorting by int is faster, what is the trick to sort a table by DateTime using the power of sorting by int ?

Answer 1

To what end are you asking this?

If you index your datetime, then you should get an efficient sort out of the column.

Better yet, set the datetimefield to be the clustered index of the table and that should be the most efficient way.

Answer 2

Are you trying to improve the performance of sorting a Datetime field by doing some trick? If so I would leave SQL Server to it's own optimizations and keep your code clear and simpler to read. Have an index on your Datetime field.

Answer 3

Assuming all other things are equal (both columns are similarly indexed or not indexed), sorting int columns should be theoretically faster since they are smaller (4 bytes vs. 8 bytes) and this can make individual comparisons faster.

Answer 4

int is faster because you'll have to hit the hard drive less to pull the data off of it to do the sorting. ints are also smaller than datetimes.

In a star schema it is common to use an integer to store a pointer to another table that has the actual date time on it. This can also be useful for when the datetime is stored on the other table and also has the dateparts such as DayOfYear or Hour or Second broken out into individual columns. These columns can then be indexed in order to improve performance on queries for dateparts.

Answer 5

If you are trying to optimize date/time sorting and it really matters, you may want to consider a smalldatetime type.

It's a 32-bit number, so it sorts as quickly as int, but compared to datetime, has a smaller range of acceptable dates (1900-2079) and is rounded to the nearest minute.