Discussion on index invalidation in SQL Server database

There are questions about indexing failures in the database, and there are related discussions on the web. However, they were discussed for the Oracle database. So when is the index in the SQL Server database

Will fail. Sum up, but I haven't been tested. Without testing there is no say, here for your own reference only.

First, the so-called invalidation. It's not really the index that was deleted. Instead, in these cases, the DBMS does not retrieve the index list. The execution speed is the same as the speed without this index.

But then another statement is executed. The same index can work correctly. So the invalidation of an index is for an SQL statement, not for the index itself. So under what circumstances,

Exactly what type of statement is executing when the index does not work. Summarized as follows:

1. When the index field is queried for a null query. That is, when the index field is judged to be null. The statement is either NULL or is not NULL.
For example, the SELECT * from temp where time is null does not retrieve the index table on the now field. That is, the index was invalidated when this statement was executed. and then

Row SELECT * from temp where time = ' 2010-06-01 ' This will retrieve the index table. The index works again.

2. When you make a like query on an indexed field. For example: SELECT * from temp where des like '% Wang '. Some examples on the web say that the like ' xx% ' index works. I haven't tried.

3. Determine if the index column is not equal to a value. '! = ' operator. For example: SELECT * FROM temp where amount! = 0.

4. Perform an operation on the indexed column. The operations here include +-*/and other operations. Also includes the use of functions. Like what:
SELECT * FROM temp where amount+count>10 the index does not work at this time.
SELECT * FROM temp where round (amount) >10 the index does not work at this time.
That is, if the Index field column is not judged directly, it is the index column that is processed by the operation or other function. indexes do not work.

Discussion on index invalidation in SQL Server database