9

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?

2
  • If you are doing range queries then a datetime column is often a very good use of a clustered index IMO. Sep 28, 2012 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, 2012 at 15:46

1 Answer 1

5

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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