0

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?

Simple Code
  • 2,354
  • 2
  • 27
  • 56
  • Have you tested it? Get an estimated plan and look at the operator on the right. – MJH Mar 01 '18 at 11:34

1 Answers1

3

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • So do you mean that I have to create separated index for each one? – Simple Code Mar 01 '18 at 12:12
  • You have to think of the query first, do i need to ensure this query runs fast as soon as possible..if this is the case, you need to ensure it has the right indexes..It is a tradeoff between query performance and index maintenance – TheGameiswar Mar 01 '18 at 12:15
  • If I have two indexes one as the above one and the other which is only on the UserName column for this query "Select * From Persons Where UserName='Smith'" which index sql server will look up? – Simple Code Mar 01 '18 at 12:19
  • 1
    if you have `username` column as only one column in the index and if you say `select *` 1.) If the result set is low,sql will use `username`column index and then do lookup on main table to get rest of the rows .2.) if your result set is large, your query may end up not using the index as well and will tend to use other index or probably scan the main table..This concept is called Tipping Point (https://www.sqlskills.com/blogs/kimberly/the-tipping-point-query-answers/) – TheGameiswar Mar 01 '18 at 12:23