When sql server is able to use indexes and when not

As we know we should write sql queries according to indexes on the table as well we should create the indexes on the table according to sql queries. Here we will discuss about when sql server is able to use indexes and when not.  To illustrate this we are creating a table and a non clustered index on it:

--Creating table

CREATE TABLE tblMessage(

     ntMsgID BIGINT PRIMARY KEY IDENTITY,

     ntSeverity INT,

     ntLanguageID INT,

     vcText VARCHAR(100)

)

--Inserting some records into it

INSERT tblMessage

SELECT severity,language_id,LEFT(text,100)   

FROM Sys.messages

--Creating non-clustred index

CREATE NONCLUSTERED INDEX NCI_Severity

ON tblMessage(ntSeverity,ntLanguageID)

In the non clustered index NCI_Severity, first key column is ntSeverity and second key column is ntLanguageID

Rule 1: A sql query use the index if the query uses following operators with the first key column of index:

a. =

b. <= or <

c. >= or >

d. BETWEEN

e. IN or NOT IN

f. <> or !=

For example following sql queries uses the non clustered index NCI_Severity:

SELECT ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20

SELECT ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity >= 20

SELECT ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity BETWEEN 20 AND 30

SELECT ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity IN (15,16,20)

SELECT ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity NOT IN (15,16,20)

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity <> 20

Its actual execution plan:

So all of the above query is using non cluster index NCI_Severity.

Important points:

During the query compilation phase sql server tries to improve the query performances by changing the query to its equivalent syntax. In sql server following operator are equivalent to each others:

1.

a. ntSeverity BETWEEN 20 AND 30

b. ntSeverity >= 20 AND ntSeverity <= 30

2.

a. ntSeverity IN (15,16,20)

b. ntSeverity = 15 OR ntSeverity = 16 OR  ntSeverity = 17

3.

a. ntSeverity NOT IN(15,16,17)

b. ntSeverity <> 15 AND ntSeverity <> 16 AND ntSeverity <> 17

c. (ntSeverity > 15 OR ntSeverity < 15)

AND (ntSeverity > 16 OR ntSeverity < 16)

AND (ntSeverity > 17 OR ntSeverity < 17)

4. <> is also very much equivalent to

a. ntSeverity <> 20

b. ntSeverity != 20

c. ntSeverity > 20 OR ntSeverity < 20

Rule 2: If sql query use the second key column of index in query predicate then must have to use operators =, <, <=, >, >=, IN, NOT INT, BETWEEN, <> etc with the first key column of index to use the index. For example:

Following sql queries will not able to use index NCI_Severity:

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID > 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID IN (1033,1035)

Since in  where clause there is not any filter condition with first key column i.e. ntSeverity of index NCI_Severity While following sql queries will be able to use index:

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity > 20 AND ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20 AND ntLanguageID > 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID IN (1033,1035) AND ntSeverity <> 20

Important point:

Almost all cases order of predicates in WHERE clause doesn't matter since while query compilation phase sql server reorders the predicates in WHERE clause according to best performance. For example following sql queries are equivalent:

a.

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity > 20 AND ntLanguageID = 1033

b.

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID = 1033 AND ntSeverity > 20

If will check the detail of index seek operator in the execution plan of following sql query:

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity > 20 AND ntLanguageID = 1033

Predicate ntSeverity > 20 is use index seek while predicate ntLanguageID = 1033 is not able to use index seek.  Why?  Go through Rule 3.

Rule 3: If there are multiple key columns in index  sql server will choose seek operation with other key columns if and only if there must have a predicate with equal to (=) operator with the first key column of an index. For examples:

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20 AND ntLanguageID = 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntLanguageID > 1033 AND ntSeverity = 20

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20 AND ntLanguageID <> 1033

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20 AND ntLanguageID IN (1033,1035)

If will check the detail of index seek operator in the execution plan of following sql query:

SELECT  ntSeverity,ntLanguageID

FROM  tblMessage

WHERE ntSeverity = 20 AND ntLanguageID = 1033

Now it using performing index seek operations with both the predicates. 

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples