CODEWITHSUNDEEP
sql-serverquery-optimizationnon-clustered-index
Simple Code
Simple Code

Reputation: 2574

How does sql server look up in composite non-clustered index?

If for example I have composite non-clustered index as following:

CREATE NONCLUSTERED INDEX idx_Test ON dbo.Persons(IsActive, UserName)

Depending on this answer How important is the order of columns in indexes?

If I run this query :

Select * From Persons Where UserName='Smith'

In the query above IsActive which its order=1 in the non-clustered index is not present. Does that mean Sql Server query optimizer will ignore looking up in the index because IsActive is not present or what?

Of course I can just test it and check the execution plan, and I will do that, but I'm also curious about the theory behind it. When does cardinality matter and when does it not?

Upvotes: 0

Views: 220

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

SQLServer will scan the total index ,in this case it might be narrowest index..

Below is a small example on orders table i have

enter image description here

Query predicate (shipperid='G') satisfies 199748 rows,but sql server has to read total rows (998123) to get data.This is visible from the number of rows read to actual number of rows.

I found this from Craig freedman to be very usefull..Assuming you have index on (a,b)..SQLServer can effectively do below

  • a=somevalue and b=somevalue
  • a=someval and b>0
  • a=someval and b>=0

for below operations,sql server will choose to filter out as many as rows possible by first predicate(This is also the reason you might have heard to keep a column with more unique values first) and will use second predicate as a residual
- a>=somevalue and b=someval

for below case,sql server has to scan the entire index..

  • b=someval

Further reading :
Craig Freedman's SQL Server Blog :Seek Predicates
Probe Residual when you have a Hash Match – a hidden cost in execution plans:Rob Farley
The Tipping Point Query Answers:Kimberly L. Tripp

Upvotes: 3

Related Questions