SQL Server Performance

Final Order of ORDERED subquery

Discussion in 'General Developer Questions' started by Chappy, Aug 29, 2006.

  1. Chappy New Member

    Hi all<br /><br />I have quite a complicated stored proc with plenty of joins, and then orders it according to various parameters. The fields used by the ORDER BY, do not need to be in the final result set, so Im wrapping it all in a parent query which just picks the fields I need.<br /><br />Heres a simplified example..<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />select a.field1, a.field2 from <br />(<br /> select <br /> field1, field2, field3, field4 <br /> from <br /> table2 <br /> union all <br /> select <br /> field1, field2, field3, field4 <br /> from <br /> table3<br /> order by field4<br />) a<br /></font id="code"></pre id="code"><br /><br />My question is... since the parent query does not have an explicit order by (but the subquery DOES), is it safe to assume the final result set will be in the same order as the subquery ?<br /><br />Ive run some examples and I cant see anytime where they differ, but it would cause problems if one day they did differ<br /><br />Thanks, hope this isnt too confusing <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Paul<br /><br /><br /><br /><br /><br /><br /><br />
  2. FrankKalis Moderator

    quote:
    My question is... since the parent query does not have an explicit order by (but the subquery DOES), is it safe to assume the final result set will be in the same order as the subquery ?
    No! We've had a thread some time ago here where that was discussed. Will dig this out.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. Chappy New Member

    Thanks Frank, bad news is better than no news (I think <img src='/community/emoticons/emotion-1.gif' alt=':)' /> )
  4. Adriaan New Member

    The simple answer is that resultsets are never ordered if there is no ORDER BY clause for the resultset. Doesn't matter if you're referring to a table or a derived table.

    Without an ORDER BY, the rows may still come out in the order you wanted, but that is pure coincidence and may not be repeatable.

    One thing that wasn't discussed: do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?
  5. FrankKalis Moderator

  6. Adriaan New Member

    To answer my own question, "do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?"

    ... the answer would appear to be "no".
  7. Adriaan New Member

    ... but the order is again lost when you run a query on the ordered view that is JOINed to a table that is not included in the view itself.
  8. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    To answer my own question, "do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?"

    ... the answer would appear to be "no".
    Q: do you still have to use an ORDER BY when querying a view that already contains the ORDER BY clause ?
    A: Yes

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  9. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />To answer my own question, "do you have to use ORDER BY when querying a view that already contains the ORDER BY clause you need?"<br /><br />... the answer would appear to be "no".<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Successfully confused yourself. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />The answer should be: Yes. It is always the outermost SELECT that needs an ORDER BY to be guaranteed.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. Adriaan New Member

    I was afraid of that! So there is no point in using ORDER BY for the main query in a view definition? (And yes, I know it takes a TOP 100 PERCENT clause if you still want to use ORDER BY.)

    The thing is, I was doing some tests querying a view that was ordered DESC in itself. The results appeared to come out in perfect DESC order, which only changed when I added another table to the query - at which point the order got messed up completely.
  11. Roji. P. Thomas New Member

    This is a breaking change in SQL server 2005, well, atleast for those who depend on the TOP 100 PERCENT workaround to Order in a view.<br /><br />Have a look at<br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms143179.aspx>http://msdn2.microsoft.com/en-us/library/ms143179.aspx</a><br /><br />and<a target="_blank" href=http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx>http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx</a><br /><br />(Frank, dont tell me that you are not subscribed to this blog [<img src='/community/emoticons/emotion-1.gif' alt=':)' />])<br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  12. Adriaan New Member

    Roji,
    quote:In SQL Server 2005, the ORDER BY clause in a view definition is used only to determine the rows that are returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
    As this is the list of "breaking changes", doesn't that imply that this was different in SQL 2000?
  13. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan
    As this is the list of "breaking changes", doesn't that imply that this was different in SQL 2000?
    Yes. It used to work in 2000, even though it was never guaranteed. Now it breaks all the code that depended on this "feature"

    You will be amazed by the number of people complaining a bout the "lost order" in the public groups

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  14. Adriaan New Member

    I would be surprised if I ever checked the public groups. I just never do.[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]
  15. mmarovic Active Member

    It looked like it worked on sql server 2000, but actually it didn't always work. I can't find an example right now, but it was something like:


    select *
    from (select top 100 percent col1, ...
    from table
    order by col1 desc) as t

  16. Adriaan New Member

    Mirko, that's when using a derived table. My question was about ordered views.
  17. mmarovic Active Member

    I am just guessing, but it may be that view is treated as derived table in query. Logicaly it is the same (if view is not indexed).

Share This Page