If I've got a area inside a table of some date type and that i realize that I will be searching it using evaluations like between, > or < rather than = could there be a very good reason not to include a catalog for this?

You will find much more bad reasons.

However, a catalog around the search column might not be enough when the index is nonclustered and non-covering. Queries such as this are frequently good candidates for clustered indexes, however a covering index is equally as good.

The only real reason to not add a catalog on the area you will explore would be that the price of maintaining the index overweights its benefits.

This might happen if:

  • You've got a really tough DML you are cooking
  • The presence of the index causes it to be intolerably slow, and
  • It's more essential to possess fast DML compared to fast queries.

When not the situation, then just produce the index. The optimizer just will not utilize it whether it thinks it isn't needed.

This can be a great illustration of why this is because much art as science. Some factors:

  • How frequently is data included in this table? If there's far more reading through/searching than adding/altering (the entire reason for some tables to dump data into for confirming), then you need to add too much with indexes. You clustered index may be needed more for that ID area, but you could have lots of multi-column indexes (in which the date fields comes later, with posts listed earlier within the index perform a good job of lowering the result set), and covered indexes (where all came back values have been in the index, therefore it is extremely fast, like you are looking for the clustered index to start with).

  • When the table is edited/put into frequently, or else you have limited space for storage and therefore can't have a lot of indexes, then you need to be more careful together with your indexes. In case your date criteria typically gives an array of data, and also you don't search frequently on other fields, then you definitely could provide a clustered index to this date area, but think several occasions before you accomplish that. You clustered index standing on an easy autonumber area is really a bonus for the indexes. Non-covered indexes make use of the clustered index to zip towards the records for that result set. Don't slowly move the clustered index to some date area unless of course the huge most of your searching is on that date area. It is the nuclear option.

  • If you cannot have lots of covered indexes (data changes a great deal up for grabs, there's only a little space, your result sets are large and varied), and/or you actually need the clustered index for an additional column, and also the typical date criteria gives an array of records, and you've got to look a great deal, you have problems. If you're able to dump data to some confirming table, do this. If you cannot, then you will need to balance each one of these competing factors carefully. Maybe for that top 2-3 searches you minimize the end result-set posts around you are able to configure covered indexes, and also you allow the relaxation make due having a simple non -clustered index

You can observe why good db people ought to be compensated well. I understand many of the factors, however i envy individuals to can balance each one of these things rapidly and properly without needing to perform a large amount of profiling.

Don't index it If you wish to scan the whole table each time. I'd want the database to perform a range scan, so I'd add the index, however i use SQL Server and it'll make use of the index generally. However different databases many not make use of the index.

With respect to the data, I'd go beyond that, and suggest maybe it's a clustered index if you are likely to be doing BETWEEN queries, to prevent the table scan.

While a catalog assists in querying the table, it will likewise decelerate card inserts, updates and removes somewhat. For those who have much more alterations in the table than queries, a catalog can hurt the general performance.