Column Order in an Index

  • Comments posted to this topic are about the item Column Order in an Index

  • A good article.. 🙂

  • Nice post 🙂

    Thanks

  • Poor summary about column considerations in an index. For the complete picture, I suggest http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/">

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    Wilfred
    The best things in life are the simple things

  • Previous link can be found here :

    http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order

    Franky L.

  • An article so absolutely shallow and superficial as to be entirely braindead. I won't go into why (if you don't already know, then you shouldn't be writing SQL code in the first place) but I will correct one error. An index scan is not "much better" than a table scan. It's exactly the same cost as scanning a table as wide as that index. In this example, if the table had no other rows than last name and first name, the cost would be identical whether table or index scanning, and the QO would only favor the index scan on the assumption that the index is more likely to already be cached.

  • Sorry, you shouldn't be writing SQL if you think this isn't patently obvious.

  • Further to your scenario, Can you help me with your feedback and comments on the below scenario

    Considering the same scenario as yours

    Scenario 1: I have one more index lastname , emailid then when i fire a query then how will be the execution plan be for following WHERE clauses

    1. WHERE LastName='ABC'

    2. WHERE LastName='ABC' AND EMAILid ='abc@xyz.com'

    3. WHERE LastName='ABC' AND EMAILid LIKE 'abc@%'

    4. WHERE LastName='ABC' AND firstName ='PQR'

    Thanks in advance

    Regards,

    Kiran R. Khot

  • Kiran, B-trees really aren't that complex to understand. In all four of your examples, there will be an index seek. In case 1 and 4, the index will then be range-scanned for all ABC values (in case #1, this is optimal anyway). In case 2 and 3, the second WHERE predicate is also an index key, so the seek will proceed directly to the first matching value from the result set.

  • Thanks for the article Sarvesh. You may want to spend a little more time on the next one, just nail down why these things occur. As you showed, it's not simply the order in which columns are stored, rather, it's which column is on the front, the leading edge, of the index. Answer the question, why does the leading edge matter, and you'll have a much improved article.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Good Article to understand Non clustered index usage.

  • The article may be short and short on detail, however, the really good thing was that it lead me to this discussion and the links here. Thank you!:-)

  • Thanks for the feedback Grant. Much appreciated. Will keep that in mind for my next article.

  • Sarvesh, This was a good article, with the potential to be much better if you were to expand on it. You leave out a lot of why things work this way leaving it at a very entry level atricle.

    I think it great that you did the article, but please - put more effort into it. Why does the index work this way? How do statistics impact it? What is the difference between an index scan and an index seek? etc...

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Please don't laugh at me for asking this question.

    Why would you create a compound index with both last name and first name together? Why not two separate indexes where one is for last name and the other is for first name. That should handle any combination that you have in your where clause.

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply