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.
exec Event.ReadEvents @startDate='2012-11-19 00:07:30', @endDate='2012-11-19 00:07:35'
The query took longer than a minute without index. Surprisingly and very happily, the time reduced to 6 milliseconds. I love index.
CREATE NONCLUSTERED INDEX [IX_Events_CreatedDate] ON [dbo].[Events] ( [CreatedDate] ASC ) ON [PRIMARY]
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.