Search code examples
sql-serverquery-optimizationnon-clustered-index

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?

Solution

  • 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