7

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?

  • 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
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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