July 22, 2013 at 9:11 am
We tend to avoid ORDER BY clauses in stored procs because we let the application do the sorting. A lot of times the app can sort it faster. Thus the execution plan does not include a sort, and since the proc is parameterized, there's no telling how much data SQL would have to sort before returning anything.
Tony
------------------------------------
Are you suggesting coconuts migrate?
July 22, 2013 at 10:41 am
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
Actually it is guaranteed neither that the way the data is stored (the physical storage order) nor the order in which the rows are returned (a property of the optimiser's query plan including any effects of parallel execution, advanced scans, existence of other indexes that are "good enough", parallel scanning, use of advanced scan, etc etc) is the clustered index order; all that's guaranteed is that the logical storage order (which can be quite different from the physical storage order) matches the clustered index order.
And it's terrifying that more than a third of people get this wrong, because that suggests that there is rather a lot of SQL out there that works only by accident and is just waiting for a small change (adding non-clustered index that covers query, or upgrading the server, or upgrading the storage, or growing the amount of data, or .....) to break and deliver stuff in an order that causes the application to do something wrong.
Tom
July 22, 2013 at 3:19 pm
Nice and easy relational theory question.
Can not even begin to count the times were this basic piece of relational set logic has not been understood.
:w00t:
July 23, 2013 at 2:16 am
L' Eomot Inversé (7/22/2013)
And it's terrifying that more than a third of people get this wrong, because that suggests that there is rather a lot of SQL out there that works only by accident
It also shows that there are an awful lot of people who answer the QotD but then never bother reading the associated threads, as this behaviour is mentioned regularly. Which in itself is worrying, given the numbers of questions over the years that have had incorrect/misleading answers which are then resolved in the ensuing discussion...
July 23, 2013 at 2:37 am
L' Eomot Inversé (7/22/2013)
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
Actually it is guaranteed neither that the way the data is stored (the physical storage order) nor the order in which the rows are returned (a property of the optimiser's query plan including any effects of parallel execution, advanced scans, existence of other indexes that are "good enough", parallel scanning, use of advanced scan, etc etc) is the clustered index order; all that's guaranteed is that the logical storage order (which can be quite different from the physical storage order) matches the clustered index order.
Yes, what you say is quite right. I was using the word "way" in its most general sense, to include logical as well as physical order. I didn't point out the distinction between logical and physical to Kapil since the data isn't guaranteed to be returned in either order.
John
July 25, 2013 at 9:11 am
bitbucket-25253 (7/20/2013)
If any one misses this basic question ... woe to the place where they work
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 26, 2013 at 4:43 pm
SQLRNNR (7/25/2013)
bitbucket-25253 (7/20/2013)
If any one misses this basic question ... woe to the place where they work+1
+1
Not all gray hairs are Dinosaurs!
July 30, 2013 at 8:16 am
This is my first interview question for anyone who will be working with SQL. I usually "mix it up" a bit by stating that the table has a clustered PK, several other indexes, etc. and then ask about various simple SELECT statements. Probably 9 out of 10 candidates give an incorrect answer. When asked to explain the reason behind their answer, their explanations are very interesting. What is most disturbing is that when I ask "would you bet your life on it?" most will say "yes" to their incorrect answer.
Needless, to say, the interview process is cut short. "Next..."
August 1, 2013 at 4:02 am
Miles Neale (7/26/2013)
SQLRNNR (7/25/2013)
bitbucket-25253 (7/20/2013)
If any one misses this basic question ... woe to the place where they work+1
+1
+1 once more... 🙂
August 1, 2013 at 6:13 am
sqlnaive (8/1/2013)
Miles Neale (7/26/2013)
SQLRNNR (7/25/2013)
bitbucket-25253 (7/20/2013)
If any one misses this basic question ... woe to the place where they work+1
+1
+1 once more... 🙂
This is why we need a "+1" or "like" signal that doesn't clutter-up the thread. 🙂
August 1, 2013 at 6:14 am
Mike Dougherty-384281 (8/1/2013)
This is why we need a "+1" or "like" signal that doesn't clutter-up the thread. 🙂
+1!
(oops)
August 2, 2013 at 9:04 am
I think the bottom line is that SQL Optimizer will return rows in the best order it sees fit based on execution plan. There are definitely cases, especially with larger tables using parallelism, when even the order of the clustered index is not obeyed/returned to the calling client.....
March 25, 2014 at 3:29 pm
hesitated for a moment thinking this was a trick question 😉
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply