November 12, 2014 at 8:15 am
Thank you for your input. I am agree with you. but... can we refer to this link?
http://use-the-index-luke.com/sql/partial-results/top-n-queries
I think "Order By" just do for "Top (n)" rows not for whole set of rows. We need 'sort' the rows, but not "Order BY". Am I right?
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
November 12, 2014 at 9:06 am
chgn01 (11/12/2014)
Thank you for your input. I am agree with you. but... can we refer to this link?http://use-the-index-luke.com/sql/partial-results/top-n-queries
I think "Order By" just do for "Top (n)" rows not for whole set of rows. We need 'sort' the rows, but not "Order BY". Am I right?
No, you're not. The only way the TOP shortcuts is if an index has already retrieved the data in the ORDER BY order. Without having the data coming in in proper order, you can't cut the TOP off.
Unless you have no ORDER BY, but an ORDER BY has to be applied before you apply top.
If the data is in date order, ascending, but I want the top ten rows by last name, we can't take the first 10 rows and stop.
November 13, 2014 at 8:11 am
Nice question (now that the duplicate answer problem has been fixed).
But I really hate the explanation. It refers to textual order as logical order, contradicting itself since it has already said what the correct answer is for the logical order and it certainly is not the textual order. There is a pointless mention of physical order, which is nowhere discussed in the question, answers, or explanation except for that one pointless mention which doesn't appear to tell us anything; perhaps it is intended to say that the logical order should be called the physical order which would be rather silly as the BOL page referenced in the explanation clearly states that the physical order may be different.
Tom
November 13, 2014 at 8:53 am
I waited a day to see if the answers got corrected so that there weren't any duplicates.
November 13, 2014 at 3:24 pm
chgn01 (11/12/2014)
Thank you for your input. I am agree with you. but... can we refer to this link?http://use-the-index-luke.com/sql/partial-results/top-n-queries
I think "Order By" just do for "Top (n)" rows not for whole set of rows. We need 'sort' the rows, but not "Order BY". Am I right?
I'm not entirely sure where you are heading with this, so I will give two replies.
1: With or without TOP, the final order in which rows are returned to the client is not guaranteed by anything other than an ORDER BY on the outermost query. TOP does allow you to add an ORDER BY clause at an inner level (e.g. subquery or view) but that ORDER BY determines only which rows are selected; only the outermost ORDER BY can provide double duty in specifying sort order AND specifying the TOP.
But no guarantee of order does not mean that SQL Server does not order. If the input values are 3, 5, 8, 10, 2, 16, 1 and you have to return the TOP(1), you do need to have some sort of sorting.
2: The article you reference mentions some optimizations in the execution plans for Oracle and DB2, but does not mention (in the main text - there is a reference to an appendix) that SQL Server has these too. For TOP with a small value, SQL Server will use a TOP-N sort opertor, which uses a different sort algorithm that is optimized for returning only the first N rows after sorting. (Simply put, instead of storing the entire result set in memory and/or tempdb, it will store only the top-N rows encountered "so far" and add each new row in the appropriate place or discard it, based on its sort order. The sort mechanism itself is slower but it saves a lot of storage, hence this method is only used for small values of N because otherwise the extra computation exceeds the savings).
The article also mentions "pipelined" queries. If you read the text carefully, you will see that this is equivalent to reading data from an index using an ordered scan, so that the data is already sorted when it is read. In that case only, the execution plan can just pull and return the first N rows and then exit, because the ordered index scan kind of implies a sort.
But again, this is all physical execution order, not logical order.
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply