April 23, 2009 at 9:34 am
I think the answer is correct, but I have added a warning to the explanation so that people that might not read the discussion will see it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 23, 2009 at 11:37 am
I don't see how the correct answer is debatable. What's debatable is the use of the clustered index and whether one should use TOP without an ORDER BY.
Of course it will change the answer if you change the code and add an ORDER BY clause. But then, you're changing the question.
The results of the specific code presented is logical and repeatable. If you use TOP 1 on a field that is not part of an index and is not in an ORDER BY clause, you will return the first natural physical record.
I agree that one should never write code like this but it doesn't make the answer wrong.
April 23, 2009 at 12:44 pm
skjoldtc (4/23/2009)
I don't see how the correct answer is debatable.
The answer is debatable because the answer isn't guaranteed. It would not be a bug in SQL Server for it to return a different value. As someone else mentioned, an easily envisionable change would be for the server to use a cache and avoid both an index and a table scan. Another possible change would be a temporary index based on last access. Neither of these are way out, impossible ideas.
If you use TOP 10 on a table with 20 rows, you're guaranteed to get 10 rows. But you're not guaranteed to get those rows in any particular order without an Order By clause. I would not be willing to bet serious money that it's impossible to devise a scenario that produces different results today.
April 24, 2009 at 4:32 am
Table data works like this. If you have a clustered index on the table it will sort the data in the table according to the fields you added in the index. If there was a clustered index on the field ID on #t then the data would have been sorted by ID. A nonclustered index do not do that and therefore the data will be as it was entered in the table and A was thus the right answer.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 24, 2009 at 10:10 am
Manie Verster (4/24/2009)
Table data works like this. If you have a clustered index on the table it will sort the data
That describes the physical storage. But the language specification doesn't require that that physical storage be taken into account for any particular query. It's something that the optimizer CAN use, and as a practical matter DOES use, but not something that it is REQUIRED to use.
Or to put it another way, if you're writing software that is used where it's a matter of life or death (hospital, traffic control), you don't want to depend upon a side effect to produce the right answer. You want your results to be as absolutely reliable as its possible for them to be.
April 24, 2009 at 1:57 pm
john.moreno (4/24/2009)
Manie Verster (4/24/2009)
Table data works like this. If you have a clustered index on the table it will sort the dataThat describes the physical storage. But the language specification doesn't require that that physical storage be taken into account for any particular query. It's something that the optimizer CAN use, and as a practical matter DOES use, but not something that it is REQUIRED to use.
Or to put it another way, if you're writing software that is used where it's a matter of life or death (hospital, traffic control), you don't want to depend upon a side effect to produce the right answer. You want your results to be as absolutely reliable as its possible for them to be.
Of course you will never do that. Hence the warning in the answer to always use ORDER BY with TOP. But that is not the point of the question as I see it. The point is that with only a NON-CLUSTERED index your data will not be sorted.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 29, 2009 at 11:09 am
There's something I disagree with in this paragraph: "Note that this works in the current version of the optimizer, but it is subject to change. DO NOT use TOP WITHOUT an Order By clause."
The fist sentence is fine and true. The second sentence is silly: if I use top n to select n records form a set and don't care which n (or even whether I always get the same n or not) that's a legitimate use of top. Not forcing an order allows the optimizer greater freedom to produce a cheaper answer. The second sentence quoted above would preclude that, and thus encourages inefficient code.
If the second sentence had read "DO NOT use TOP WITHOUT an Order By clause, UNLESS you DO NOT CARE which records are returned and DO NOT EXPECT to get the same records every time you do it to the same table with the same data in it" I would have agreed with it. Without the qualification I can't.
BTW, I agree with many other commenters that the question could be considered suspect anyway - as someone said, the right answer is not present; but as someone else said, the two sentences following the (not really right) answer make it pretty clear what the question was getting at, so (even though I disagree with the second of them because it suggests too much restriction) I think they legitimize the question in a forum like this.
Tom
May 1, 2009 at 3:37 pm
The answer text says it all:
Since the index created is a nonclustered one, when "top 1" is queried for; we get the value 'A' and not '$'. The data remains in the natural order it was inserted in. This is not guaranteed, but on a small table that fits on one page, this will be the case.
The answer is not guaranteed, therefore it is not correct. No if's and's or but's about it, it's not correct.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2009 at 9:32 am
Steve Jones - Editor (4/23/2009)
I think the answer is correct, but I have added a warning to the explanation so that people that might not read the discussion will see it.
Good Idea. I highly recommend that any obvious violations from good practices should be noted in the explanation (like Steve did). That would make these tests even more useful, as they would help to educate developers and new DBAs.
Paul DB
May 2, 2009 at 10:40 am
Tom,
the advice we give is the general advice for anyone. It is not an absolute, and I wouldn't qualify it. The advice is "do not use top without an ORDER by clause."
If you don't care about the order, then you can ignore the advice, but that's the general advice.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply