When indexes are not available in SQLServer

Question

I'm looking into indexes in SQLserver.

In SQLserver, when an index is created for a string type (ex.char,nvarchar) and a numeric value is specified as a search condition, the index cannot be used.

Are there any other examples like this where the index cannot be used depending on the type?

Answer 1

So the case you are talking about is:

SELECT * FROM tbl WHERE indexstringcol = 99

You are correct that the index will not be used in this case. Or more precisely, it cannot be used as intended with an Index Seek. An Index Scan is still possible.

On the other hand, if you have the query

SELECT * FROM tbl WHERE indexedintcol = '999'

In this case, the index can still be seeked.

The reason for this is is that when two data types meet, SQL Server applies a strict data-type precedence, and the data type with lower precedence will converted to the type with higher precedence. Numeric data types has higher precedence than strings.

This means that in the second example, the string literal is converted to integer, and the columns is unaffected.

In the first example, the string column is converted. The index is organised for string values, so that '100' comes before '99', but since that is not true for numbers, the index is not useful.

If there are other such cases? Yes, the general rule is that when an indexed column is implicitly converted, this prevents an efficient use of the index with an Index Seek.

There are some exceptions, though. For instance, this still uses the index in the best way:

SELECT * FROM tbl WHERE indexeddatecol = @datetimeval

This is because they have a special rule for this in the optimizer.

Answer 2

the index cannot be used.

What do you mean with?

If an index is used for a query depends on index design and the query filter condition; which you haven't mentioned in any way.

Check the query execution plan of your query and may share it via

https://www.brentozar.com/pastetheplan/

Answer 3

if you use a cast or convert function on a indexed column, the index generally useless as a seek can not be used and a full scan is required. if instead you cast the compare value to the column value than the index could be used.