SELECT TOP 1 on Primary Key?

  • AdrianLParker+sqlservercentral.com

    SSC Enthusiast

    Points: 125

    Lynn Pettis - Monday, November 12, 2018 10:07 PM

    The problem is that your WHERE clause says WHERE somePrimaryKe y = 3 OR anyColumn > 6.  This means that the results set returned will contain the row of data where somePrimaryKey = 3 (if it exists) and all the rows of data where anyColumn > 6 (if any exist).  SQL has to look for data that matches either OR both conditions.

    Yup, wrote in a rush sorry. I meant if it had an AND clause. In which case I would hope the index was checked first. I assume that would be faster? It would be less generic though, coding-wise, as it would mean you now have the special case where you check whether the column(s) are indexed before you decide which conditions to check first.

    All I'm saying is that internally I don't know if the DB is written for speed optimization, or more generic programming.

  • Lynn Pettis

    SSC Guru

    Points: 442180

    AdrianLParker+sqlservercentral.com - Tuesday, November 13, 2018 10:07 AM

    Lynn Pettis - Monday, November 12, 2018 10:07 PM

    The problem is that your WHERE clause says WHERE somePrimaryKe y = 3 OR anyColumn > 6.  This means that the results set returned will contain the row of data where somePrimaryKey = 3 (if it exists) and all the rows of data where anyColumn > 6 (if any exist).  SQL has to look for data that matches either OR both conditions.

    Yup, wrote in a rush sorry. I meant if it had an AND clause. In which case I would hope the index was checked first. I assume that would be faster? It would be less generic though, coding-wise, as it would mean you now have the special case where you check whether the column(s) are indexed before you decide which conditions to check first.

    All I'm saying is that internally I don't know if the DB is written for speed optimization, or more generic programming.

    All I can say at this point, it depends.  You need to test, test, and test again to be sure any queries you are writing are correct and performant.

Viewing 2 posts - 31 through 32 (of 32 total)

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