Post reply

Index Scan vs Index Seek

  • June 12, 2013 at 8:53 am

    #1623385

    Gail

    I think this is on-topic and you seem to know as much (or more) than anyone on the subject of index behavior that I'd like to ask a question that I've posted before but no one has ever had a definitive answer.

    I notice that on many queries, depending on the makeup of the WHERE clause, that SQL will be doing Index Scans that no amount of fiddling around seems to help. I see this most often when I'm returning all rows with no WHERE clause at all (which sort of makes sense).

    But I can add an artificial filter like 'WHERE primarykey > 0" and that seems to force the compiler into doing an Index Seek on that key. I've wondered about this for years now. Is this really changing the execution of the query in any significant way? Or is it just a 'phantom" effect of some kind?

    I've attached two Execution Plan files that were run against the same table to give an example of this behavior.

     

  • June 12, 2013 at 8:58 am

    #1623388

    Steven Willis (6/12/2013)

    But I can add an artificial filter like 'WHERE primarykey > 0" and that seems to force the compiler into doing an Index Seek on that key. I've wondered about this for years now. Is this really changing the execution of the query in any significant way? Or is it just a 'phantom" effect of some kind? 

    http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

    Also discussed in a chapter in "Pro SQL Server 2012 Best Practices"

    Please in future post new questions in a new thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 16 through 16 (of 16 total)

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