On What operations indexs do not work well or are not used

I am creating sql queries and I have read in one book that when using NOT operator or LIKE operators Indexes do not work. How much true this statement is. How can we avoid this if the statement is true. How a query should be made to do the same work avoiding these operators.

What all other areas are there in sql server where Index开发者_运维知识库es are deferred.

Like statements that wildcard the leftside can not use an index if one is defined for the column:

WHERE column LIKE '%abc'
WHERE column LIKE '%abc%'

But either of these can use an index:

WHERE column LIKE 'abc%'
WHERE column LIKE 'abc'

Frankly, use LIKE for very simple text searching - if you're really needing a text search that performs well, look at Full Text Searching (FTS). Full Text Searching has it's own indexes.

The decision really comes down to the optimizer for which index to use, but something that ensures that an index will not be used it to wrap column values in function calls. This for example will not use indexes:

WHERE CHARINDEX(column, 'abc') > 0
WHERE CAST(column AS DATETIME) <= '2010-01-01'

Anywhere you are manipulating table data--especially changing the data type--will render an index useless because an index is of the unaltered values and there's no way to relate to altered data.

For like looks to this article: SQL Performance - Indexes and the LIKE clause

For NOT operator using indexes will depend on particular query.

From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan. Use this to analyze the effectiveness of your query. Based on the results, you may need to use another query or add better indexes. E.g. table scan means that indexes are not used, bookmark lookups mean you should limit the rows or use a covering index, if there's a filter you might want to remove any function calls from the where clause, sort can be slow.

The search term you need to explore this issue in more depth is sargable.

here is one article to get you started: http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx