Will adding an index to a
datetime column increase performance if a
WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that
If yes, should the index be clustered or non-clustered and why?
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 only takes a minute to sign up.Sign up to join this community
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,