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.

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

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s