March 10, 2009 at 12:51 pm
I know that omitting an ORDER BY clause when using TOP 1 will return the first record found in the table. My question is, what is the “first record found” based upon? I assumed that if you have a clustered index on the table, that would be the basis for which record is found first. Is that correct? What if you don’t have a clustered index? I've Googled this and found many answers, but none that clearly define the method(s) by which SQL determines the "first record found".
March 10, 2009 at 1:09 pm
Tony (3/10/2009)
I know that omitting an ORDER BY clause when using TOP 1 will return the first record found in the table. My question is, what is the “first record found” based upon?
Nothing specific. It's whatever record the query processor gets first
I assumed that if you have a clustered index on the table, that would be the basis for which record is found first. Is that correct?
Not necessarily
What if you don’t have a clustered index?
See above
I've Googled this and found many answers, but none that clearly define the method(s) by which SQL determines the "first record found".
Probably because in a table, there's no meaning to 'row order'. A table in an unordered set. If you say TOP (1) without specifying an order, you're asking SQL for a record. Any record. It will get one, which one is not guaranteed in any way. It may even differ from one query to another
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
March 10, 2009 at 1:20 pm
So it can be random?
March 10, 2009 at 1:21 pm
It sure can. Doesn't mean it will be random, just means you can't count on which record it will be.
Edit: Well, unless there's only one row in the table, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 1:28 pm
Thank you both for the replies! My questions were for my own knowledge, but they stemmed from a query that one of our developers uses constantly:
SELECT TOP 1 * FROM {TableName} WHERE RecordID = {ID}
In the table, the RecordID column is the primary key, and therefore is unique. So, while I realize that using the TOP 1 in this instance is completely useless, I'm wondering if using it is also slowing down his queries. What are your thoughts?
March 10, 2009 at 1:55 pm
Tony (3/10/2009)
Thank you both for the replies! My questions were for my own knowledge, but they stemmed from a query that one of our developers uses constantly:SELECT TOP 1 * FROM {TableName} WHERE RecordID = {ID}
In the table, the RecordID column is the primary key, and therefore is unique.
May I suggest a good SQL manual for said developer. Applied across the side of the head.;)
Does he doubt that the DB knows how to enforce uniqueness?
Klap him a second time for using SELECT *.
So, while I realize that using the TOP 1 in this instance is completely useless, I'm wondering if using it is also slowing down his queries.
Unlikely.
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
March 10, 2009 at 2:04 pm
I just tested this:
select top 1 *
from dbo.Numbers
where number = 10;
select *
from dbo.Numbers
where number = 10;
There is a Top step in the first one with a cost of 0.0000001, which isn't in the second one. So, yeah, technically there may be a slight difference in the two, but it's really too small to matter in any case.
Just shows that the person who wrote it is operating on some rote memorization, instead of actually understanding what he's doing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2009 at 2:11 pm
I'm crying from laughing! GilaMonster, the SQL book upside the head has already been considered - we're just trying to determine which book in our vast library here in our office will have the most impact!
In all seriousness, we already have a meeting scheduled for tomorrow morning to review some of his SQL work so we can show him what he's doing wrong, and where he can improve performance in his applications (this was only 1 very small example of incorrect SQL coding).
Thank you both again for your responses! Your help is much appreciated!
March 10, 2009 at 9:39 pm
Tony (3/10/2009)
I'm crying from laughing! GilaMonster, the SQL book upside the head has already been considered - we're just trying to determine which book in our vast library here in our office will have the most impact!In all seriousness, we already have a meeting scheduled for tomorrow morning to review some of his SQL work so we can show him what he's doing wrong, and where he can improve performance in his applications (this was only 1 very small example of incorrect SQL coding).
Thank you both again for your responses! Your help is much appreciated!
Not trying to be a smart aleck about this but, you guys interviewed him and, apparently, thought he knew SQL. Showing a person what he's done wrong can be very, very intimidatng especially if a group of people show up to "tutor" him in a mass code review. It would probably be much better if you explained these types of things in a group "Lunch'n'Learn" or in a one-on-one with someone that is actually qualified to do a code review and also has good mentorship qualities. Unless the guy is an absolute butt-head, he'll strongly appreciate a senior member taking him under is private wing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 9:40 pm
Oh yeah... almost forgot... I'd cancel that meeting...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2009 at 6:37 am
Jeff - he is a beginner, this is his first job out of tech school, and we hired him because we saw his potential for learning. I never suggested that we thought he knew SQL. His applications are a few of our smallest where poor coding techniques will have little (if any) impact. He is extremely open to code reviews and is eager to learn.
Since you didn't offer any answers to my questions, jumping in at this point to question or criticize our hiring techniques and offer suggestions on how to speak to him is way out of line and not appreciated.
March 11, 2009 at 7:02 am
Jeff's right. That does have the sound of a sort of lynch mob kind of thing. Could be misinterpreting it, but it sounds that way. I've never yet seen anything like that be productive.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 7:20 am
Look - I appreciate the answers to my questions - I really do. You've been most helpful in that respect. But I honesly feel that, until you know the situation in our office and the people involved, it's wrong to judge how we deal with any given situation. I'm certainly not trying to be a jerk here, but the truth is that you really are misinterpreting the situation. This guy happens to be one of those people that begs for input, and is extremely eager to learn. That's why we hired him. A person can be taught the technology, but you can't teach the attitude and enthusiasm. We put this guy on very small projects while he's learning, and now that we see what he's doing, we can tutor him on things that he's doing incorrectly. Also, I never mentioned anything about a group discussing it with him. It will only be me and our senior developer. We've already had a few other meetings like this with him on VB coding, and he was superb at accepting the input (not criticism), and learning from it. I truly believe that someday (probably soon), this guy is going to be a great developer.
Again, thank you very very much for answering my questions. I truly do appreciate your help!
March 11, 2009 at 8:11 pm
Tony (3/11/2009)
Jeff - he is a beginner, this is his first job out of tech school, and we hired him because we saw his potential for learning. I never suggested that we thought he knew SQL. His applications are a few of our smallest where poor coding techniques will have little (if any) impact. He is extremely open to code reviews and is eager to learn.Since you didn't offer any answers to my questions, jumping in at this point to question or criticize our hiring techniques and offer suggestions on how to speak to him is way out of line and not appreciated.
Heh... you didn't say any of that in your OP... and who was the one that thought a book up the side of the head was funny? I'm not the one out of line here, my firend.;)
Now... let's start over. I think it's a fine thing that you're trying to do now that I know what THAT actually is. Code reviews and the like are good... but it's easier to learn the right way instead of doing something wrong and having to unlearn it during a code review. I've had great success with matching up junior/newbie developers with the senior ones to write something other than unimportant stuff... and I allow a fair bit of extra time for any given task where such a pairing takes place so the senior developer can take the time to teach and maybe even let the junior developer drive under supervision. Maybe try that for a while and see if the magic happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2009 at 6:51 am
Sounds like it's not the shark tank it originally sounded like. Cool biz.
Good luck on getting the guy trained up. That's always a good thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply