I am using SQL Server 2008, is it ok to define an Index on a DateTime column?

Question

I heard somewhere that declaring an Index on a date column is bad for performance, but I can't find any reference on the internet.

Suggestions?

Answer 1

Why should it not be ok to create an index on a date ?

If you regularly perform searches on that column, or sort, I think it is a good idea to put a index on it.

Searches will be faster; updates and inserts will be slightly slower since Sql Server has to maintain the index as well, but this 'performance-loss' is so marginal that you won't even notice it

Answer 2

DateTime datatype is stored as number, so there is no performance issue with index on it.

Answer 3

It will depend on the pattern of activity run against the database. If you are doing lots of WHEREs against the DateTime then an index will probably help. If you are doing lots of inserts, and they are not in consecutive order, then your insert speed could suffer updating the index.

As in all matters of performance, test the performance using a typical workload both before and after the change, its the only way to really know if the change will help or not.

Answer 4

DateTime in SQL Server 2005 (and in SQL Server 2008 too) is stored as a 8-byte value, consisting of:

• Number of days since Jan 1, 1900 in the first 4 bytes (the date part)
• Number of 1/300 ticks from the midnight in the second 4 bytes (the time part)

It's perfectly OK to index this value.

Answer 5

If your data is based around this date and is primarily searched using date operations then it's a good idea to make the date the first column in the clustered index for the table. This way all of the data will be organised in a heap based on this date value and will result in fast date based queries.

http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx

By default the primary key on a table is created as a clustered index. This may or may not be what you want. You can change the index type in SQL Server Management Studio in the table properties editor.

Answer 6

Perfectly fine putting an index on a date field.

The question you need to ask yourself is what benefit putting an index on that date field will give you?

e.g Many stored procs use that field in a WHERE clause, thus putting an index on there will potentially help in improving performance.

Answer 7

Though it may be good to have index beware of the statistics that get outdated frequently and inconsistent performance differences. http://sqlbits.wordpress.com/2009/08/23/tip-3-beware-of-indexes-on-datetime-columns/