Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Statement evaluation precedence Expand / Collapse
Author
Message
Posted Friday, May 7, 2010 11:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
Hugo Kornelis (5/7/2010)
CirquedeSQLeil (5/7/2010)
Paul White NZ (5/7/2010)
CirquedeSQLeil (5/6/2010)
Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.

What are you referring to here Jason?
It must be hard to spot - I can't see anything wrong...


Your list has it correct, Itzik's list has it correct. The correct Answer (and explanation) has inverted the order by and top (order by then top as opposed to the document which says top then order by).


Hi Jason,

It's not my habit to disagree with Itzik, but in this case I have to make an exception.

Lemme ask you a simple question - in a query that has a TOP and an ORDER BY clause, how exactly should SQL Server process the TOP without sorting the rows first? *IF* Microsoft had decided to use a seperate clause to define the order for the TOP instead of overloading the ORDER BY clause (for instance TOP ... OVER (ORDER BY column)), then I would agree with TOP first (which includes ordering by the OVER (ORDER BY) specificion of the TOP), ORDER BY (for presentation) last. But as it stands, it simply does not make sense to do an ORDER BY after a TOP that has already included the same sort operation.

I disagree with the list and Itzik, and I agree with the order as presented in the question.

Note that, for most clauses, the logical processing order is defined by the ANSI standards. However, the TOP clause is proprietary; ANSI does not support it. And Microsoft never officially announced the logical order, so this is all speculation. Both on Itzik's and on my part.


I agree with the order presented in the question. A simple evaluation where one does something like


Select top 10 COLUMN1,column2
From SomeTable
Where Column2 is null
Order By COLUMN1 Desc


would demonstrate that. The Order By is done prior to the Top clause. I brought it up just due to the nuance of order being different.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #918245
Posted Friday, May 7, 2010 1:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:59 AM
Points: 990, Visits: 2,217
Here is MSDN article that shows processing order for SELECT:

http://msdn.microsoft.com/en-us/library/ms189499.aspx

Post #918322
Posted Friday, May 7, 2010 3:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,231, Visits: 14,940
rVadim (5/7/2010)
Here is MSDN article that shows processing order for SELECT:

http://msdn.microsoft.com/en-us/library/ms189499.aspx



Thanks for finding that article.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #918369
Posted Friday, May 7, 2010 11:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
Hugo Kornelis (5/7/2010)
It's not my habit to disagree with Itzik, but in this case I have to make an exception.

Me either However, I think there is a subtlety here that makes both interpretations correct, but Itzik's more so:

The last two items on Itzik's list are TOP then Presentation ORDER BY. The inference here is that presentation ORDER BY is separate from TOP. My take is that TOP includes an implicit ORDER BY, and the presentation ORDER BY logically comes after that.

The important distinction is that SQL Server does guarantee which rows will be qualified by TOP, given its associated ORDER BY (if any) but it does not guarantee that the same order will be reflected in the final output, even if the TOP is the last iterator in the query plan. In general, the order used to qualify rows for a TOP iterator does not say anything about the order of rows presented to the next stage of the operation.

But as it stands, it simply does not make sense to do an ORDER BY after a TOP that has already included the same sort operation.

This is exactly what I think you are wrong about here. There is no guarantee of the 'row qualification' sort order being preserved. For anyone not prepared to take my word for it (and that should be all of you) please see Conor Cunningham's blog entry on the subject. If I were feeling mischievous, I might mention 'defensive programming' at this point

I disagree with the list and Itzik, and I agree with the order as presented in the question.

I would say that both are correct, but the question is less so - since it is missing the final presentation ORDER BY step (assuming that the ORDER BY in the question is the one associated with the TOP).

FROM
Cartesian Product
ON clause filter
Add Outer Rows
WHERE
GROUP BY
HAVING
SELECT
Evaluate expressions
DISTINCT
TOP (including qualification ORDER BY if present)
Presentation ORDER BY




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #918447
Posted Saturday, May 8, 2010 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
Paul White NZ (5/7/2010)
Hugo Kornelis (5/7/2010)
It's not my habit to disagree with Itzik, but in this case I have to make an exception.

Me either However, I think there is a subtlety here that makes both interpretations correct, but Itzik's more so:

The last two items on Itzik's list are TOP then Presentation ORDER BY. The inference here is that presentation ORDER BY is separate from TOP. My take is that TOP includes an implicit ORDER BY, and the presentation ORDER BY logically comes after that.


Ahhh, even more disagreement. Nice! Good training for the grey cells.

First: My assumption so far was that this whole discussion is about queries without any subqueries, in any of the clauses (and that includes "hidden" subqueries caused by CTE's, views, inline table-valued functions, or the APPLY operator). I think we all agree that the parentheses around subqueries imply what they imply in mathematics: that (logically) the subqeries are evaluated first. I always think of it as a virtual temporary table that is created and populated by evaluating the saubquery, and then the subquery in the original query is replaced by this virtual temporarty table. Again, all logical - this is where the actual evaluation order usually is hugely different from the logical order.

If we disagree on this assumption, if you take this discussion to be about all queries including subqueries at any nesting level, than I am prepared to agree with you on the order of the ORDER BY and TOP clauses, but I'll have to start disagreeing on almost everything else

Second: Given that we now agree that this discussion is about queries with no subqueries, there is in the SQL Sever syntax no way to specify a presentation ORDER BY that differs from the ORDER BY for the TOP. There is one ORDER BY, and it always applies to both the presentation order and the TOP clause.

Third: rVadim has managed to find what I could not - the official word from Microsoft on the logical order of evaluation, as included in Books Online. In that list, ORDER BY goes before TOP.


The important distinction is that SQL Server does guarantee which rows will be qualified by TOP, given its associated ORDER BY (if any) but it does not guarantee that the same order will be reflected in the final output, even if the TOP is the last iterator in the query plan. In general, the order used to qualify rows for a TOP iterator does not say anything about the order of rows presented to the next stage of the operation.


Where in the execution plan an operator stands is indeed totally irrelevant for what Microsoft does or does not guarantee. Where in a query a clause stands is not. And Microsoft does guarantee returning results in the order of the ORDER BY of the outermost query.

So in any subquery, ORDER BY can only apply to TOP, not to presentation order, in those cases the logical evaluation order ends with ORDER BY (for the TOP only), then TOP.

In the outer query, (either a query with no subqueries, or a query with subqueries, after logically replacing all subqueries with virtual temporary tables), ORDER BY applies to both TOP and presentation order; the logical evaluation order still ends with ORDER BY (for both TOP and presentation), then TOP (which doesn't affect row ordering).


There is no guarantee of the 'row qualification' sort order being preserved. For anyone not prepared to take my word for it (and that should be all of you) please see Conor Cunningham's blog entry on the subject. If I were feeling mischievous, I might mention 'defensive programming' at this point


In that very blog entry, Conor also wriites: "You’d need to put an ORDER BY at the top of the query to guarantee the output order returned to the client". In other words, presentation order is guaranteed if the ORDER BY is on the outermost query. And Conor writes: "If an ORDER BY is used in the same scope, it qualifies rows based on the ORDER BY" - so a TOP on the same outermost query will use the ORDER BY that also guarantees presentation order.


I would say that both are correct, but the question is less so - since it is missing the final presentation ORDER BY step (assuming that the ORDER BY in the question is the one associated with the TOP).

FROM
Cartesian Product
ON clause filter
Add Outer Rows
WHERE
GROUP BY
HAVING
SELECT
Evaluate expressions
DISTINCT
TOP (including qualification ORDER BY if present)
Presentation ORDER BY


The presentation ORDER BY is only relevant for the outermost scope, and if a TOP applies to that scope, it can only use the same ORDER BY. So the ORDER BY after the TOP does nothing.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #918472
Posted Saturday, May 8, 2010 9:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
Hugo,

I don't disagree with any of that really; as I said before, I think both answers are correct, I just happen to prefer one over the other. My preference is based on:

We are talking about the logical order of processing here, so it seems wrong to me to rely on current implementation details to argue the point. Yes, presentation ORDER BY and TOP ORDER BY will be the same as the product stands - but that seems to be a consequence of the current restrictions of the language, not a logical restriction.

Let us imagine that SQL Server supports the TOP OVER() construction in the next release -which logical description would you prefer then?

Fascinating stuff

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #918609
Posted Sunday, May 9, 2010 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
Paul White NZ (5/8/2010)
(...)

Let us imagine that SQL Server supports the TOP OVER() construction in the next release -which logical description would you prefer then?


Hi Paul,

Good question!

Thinking about it made me realize that the order as described so far does not support windowing functions at all. So instead of adding just the fictional TOP OVER(), I'll add the OVER() clause in general.

Let's start with the evaluation order in Boooks Online:
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP


Since windowing functions can not be used in WHERE or HAVING clauses, but only in the SELECT (and in the fictional TOP OVER() we just added), the most logical place to evaluate them would be between HAVING and SELECT. The result would then be:
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. ordering and grouping for windowing functions used in query
9. SELECT
10. DISTINCT
11. ORDER BY
12. TOP

What makes this interesting is how TOP and DISTINCT start to interrelate. Imagine a table like this:
CREATE TABLE Demo
(KeyCol int NOT NULLL PRIMARY KEY,
OtherCol int NOT NULL);
INSERT INTO Demo (KeyCol, OtherCol)
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 5, 2 UNION ALL
SELECT 6, 3 UNION ALL
SELECT 7, 3 UNION ALL
SELECT 8, 4 UNION ALL
SELECT 9, 1;

Before reading on, consider what output you would expect from this query:
SELECT DISTINCT TOP(2) OVER (ORDER BY KeyCol) OtherCol
FROM Demo;

If we first evaluate the TOP(2) OVER (ORDER BY KeyCol), we have an intermediate result of two rows, with value-pairs (1,1) and (2,1); only the second column is selected, and the DISTINCT than reduces it to a single column - which is weird, since we asked for TOP(2).
On the other hand, if we first evaluate the column list and the DISTINCT, we'd get an intermediate result of four single-columns rows, sporting the values 1, 2, 3, and 4; the TOP now becomes very hard to evaluate, because the KeyCol column no longer exists in this result set, and even if SQL Server under the covers would still connect the OtherCol values with their source rows, we'd still not know which of the associated KeyCol values to use.

Hmmm. Maybe this is why Microsoft has not implemented TOP OVER()?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #918630
Posted Sunday, May 9, 2010 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
Heh. Awesome stuff, going to read that properly in a bit and reply - but I can't help mentioning that window functions can appear in the ORDER BY clause too



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #918655
Posted Monday, May 10, 2010 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 5, 2011 6:56 AM
Points: 1, Visits: 13
Just when I thought the "question of the day" is going down hill with non-practical (things that you would never do in real life), or too mechanical (what button do you click to do x) craps, your question came through. Thanks. BTW. Are you Tom Brown from San Diego/Seatle?
- Hoang Lam
Post #918992
Posted Monday, May 10, 2010 8:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, July 26, 2014 3:34 PM
Points: 1,293, Visits: 1,428

Glad you liked my question.

hlam-936978 (5/10/2010)
BTW. Are you Tom Brown from San Diego/Seatle?
- Hoang Lam


Not me - wrong continent - I'm in the UK.
Post #919069
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse