GilaMonster (3/20/2013)
beginner_dk (3/19/2013)
Regarding sorting order, i went through the BOL over clause and tested on a table created my self and it worked. so i thought it might be true."It worked for me once" != "It's guaranteed to work". No order by, no guarantee of order, end of story.
Now, a simple query like that will probably come back in the correct order, but that's a side effect of how the query execution engine works, not a guaranteed behaviour
The question asks "Which of the following statements can be used to provide sorted result set based in the CustomerName column" not "Which of the following statements will provide sorted result set based in the CustomerName column", so there is a single best answer, if you assume these things:
1*) The end system can sort numeric columns, it just can't sort string columns.
2*) It's ok to force the end system to sort the results, even though it'd be a lot more efficient in SQL server (and was probably already done by the server to generate the answer).
3) It ok for the RN column to be non-deterministic for any CustomerName values that are duplicated.
*1 & 2 could be combined into: The query is going to be part of a larger query, which just wants to figure out which X number of customers to use (based on their name for some reason) and will use the numbers in the larger query (so we can't just "select top #").**
** assuming this is the case, then I don't see how there could be a better answer without just adding something to break ties, since you can't do a "order by" in a subquery.
----------
Two cents from someone who just finished the same test and was wondering what was up with some of the questions / what 9 questions I got wrong/what the correct answers were...