Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column?

If yes, should the index be clustered or non-clustered and why?

share|improve this question
If you are doing range queries then a datetime column is often a very good use of a clustered index IMO. – Martin Smith Sep 28 '12 at 15:02
It might - depending on what you select from that table, and depending on how selective your date range is... – marc_s Sep 28 '12 at 15:46

1 Answer

up vote 2 down vote

Adding an index will increase performance on SELECT statements, assuming your range of dates is not sufficiently large as to force an index scan as opposed to an index seek. Adding an index will decrease performance on INSERT, UPDATE, and DELETE operations, as this new index will need to be maintained. The clustered/nonclustered question is highly dependent on how many other columns from the table you are including in the SELECT clause.

If this question is more than a homework question, and you have examples you would like to include, it would help my answer to be more complete.

Hope this helps,

Matt

share|improve this answer
Thanks. It's not homework. – pate Sep 28 '12 at 14:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.