SELECT TOP 1 on Primary Key?

Question

SELECT TOP 1 on Primary Key?

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

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

AdrianLParker+sqlservercentral.com SSC Enthusiast Points: 125 More actions · Answer 1

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: 442467 More actions · Answer 2

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.