Post reply

Usage of OR in WHERE clause

  • September 30, 2013 at 7:47 am

    #283784

    Apologies for a silly question, but I was taught in the past to avoid usage of OR in WHERE clause as it effected

    SELECT's performance and ordered SQL engine to re-scan the table, so I keep avoiding since.

    Is it a same with SQL 2008 and higher and are there any guidelines / best practice please, e.g. I use IN or CASE where possible?

    Thank you.

  • September 30, 2013 at 8:23 am

    #1654575

    ORs don't cause scans. ORs with inadequate indexing however have no way to execute other than a table scan.

    http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

    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

  • September 30, 2013 at 8:35 am

    #1654581

    Thank you for the reply.

    Assuming I have myId field which is a single segment of myUniqueId index, is it correct

    to state that there will be no difference if I will use either myId IN (1,2) or myId = 1 OR myId = 2 ?

  • September 30, 2013 at 8:36 am

    #1654583

    No difference at all. The IN is expanded into ORs in parsing.

    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

  • September 30, 2013 at 9:09 am

    #1654603

    Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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