Left Ordering of Index Keys to Utilize the Index

• er.mayankshukla

  SSCarpal Tunnel

  Points: 4190

  More actions

  September 9, 2014 at 12:00 am

  #310849

  Hi Experts,

  Till date I know that the ordering of Index key matters in a query so that we can utilize that Index.

  Let me show you with example

  Use AdventureWorks2008

  sp_helpindex 'Person.Person'

  index_nameindex_descriptionindex_keys

  IX_Person_LastName_FirstName_MiddleNamenonclustered located on PRIMARYLastName, FirstName, MiddleName

  You can see the order is LastName , FirstName and MiddleName.

  Even the Densite vector shows information in the same order.

  So,

  If you use this query:

  select * from Person.Person where LastName = 'Adams'

  I expect an Index Seek, which exactly what I see.

  But what if I use this query,

  select * from Person.Person where FirstName = 'Ken' and LastName = 'Adams'

  Here I have disturbed the Left ordering of index keys.

  But Still I see the Index Seek with the same number of Estimated Rows as shown by Density Vector

  I want to know , whether this concept is true or a Myth

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 9, 2014 at 2:04 am

  #1744033

  er.mayankshukla (9/9/2014)

  ---

  Hi Experts,

  Till date I know that the ordering of Index key matters in a query so that we can utilize that Index.

  Let me show you with example

  Use AdventureWorks2008

  sp_helpindex 'Person.Person'

  index_nameindex_descriptionindex_keys

  IX_Person_LastName_FirstName_MiddleNamenonclustered located on PRIMARYLastName, FirstName, MiddleName

  You can see the order is LastName , FirstName and MiddleName.

  Even the Densite vector shows information in the same order.

  So,

  If you use this query:

  select * from Person.Person where LastName = 'Adams'

  I expect an Index Seek, which exactly what I see.

  But what if I use this query,

  select * from Person.Person where FirstName = 'Ken' and LastName = 'Adams'

  Here I have disturbed the Left ordering of index keys.

  But Still I see the Index Seek with the same number of Estimated Rows as shown by Density Vector

  I want to know , whether this concept is true or a Myth

  You haven't disturbed anything. SQL Server will apply the predicates in your WHERE clause in whatever order yields the lowest-cost plan. So this

  where FirstName = 'Ken' and LastName = 'Adams'

  is logically the same as this:

  where LastName = 'Adams' and FirstName = 'Ken'.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden