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

Question

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?

Answer 1

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.

Answer 2

There are far more bad reasons.

However, an index on the search column may not be enough if the index is nonclustered and non-covering. Queries like this are often good candidates for clustered indexes, however a covering index is just as good.