Indexing CreatedDate


Indexing columns is a joy and adventure.
I have just indexed a datetime column to boost performance. The query had a date range, and the column didn't have index yet.

[sourcecode language="sql"]
exec Event.ReadEvents @startDate='2012-11-19 00:07:30', @endDate='2012-11-19 00:07:35'
[/sourcecode]

The query took longer than a minute without index. Surprisingly and very happily, the time reduced to 6 milliseconds. I love index.

[sourcecode language="sql"]
CREATE NONCLUSTERED INDEX [IX_Events_CreatedDate] ON [dbo].[Events]
(
[CreatedDate] ASC
) ON [PRIMARY]
[/sourcecode]

Another surprise. It took 6 minutes to create the index.

This is the stats.

I'm not sure which sort order (ASC or DESC) is better for datetime column. Maybe it's just order preference, if your query requires sorted result.