Search code examples
sqldatabasedatetimeclustered-index

Should searchable date fields in a database table always be indexed?

If I have a field in a table of some date type and I know that I will always be searching it using comparisons like between, > or < and never = could there be a good reason not to add an index for it?

Solution

  • The only reason not to add an index on a field you are going to search on is that the cost of maintaining the index overweights its benefits.

    This may happen if:

    • You have a really tough DML on your table
    • The existence of the index makes it intolerably slow, and
    • It's more important to have fast DML than the fast queries.

    If it's not the case, then just create the index. The optimizer just won't use it if it thinks it's not needed.