View is not ordered. Why?

  • Hello,

    something strange is happening on my sql server.

    I have created a view called myview and inserted an "Order by" clause.

    If i execute select * from myview i'll get unordered rows, instead if i execute the select statement which is used to create the myview i get ordered rows.

    Any idea why this happen?

    Thank you.

  • Because SQL does not honour any Order By unless it appears in the outer select statement (the one that queries the view) or there's a row-restricting TOP specified. This is by design.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi GilaMonster,

    bad design 🙁 At least sql has to inform me about this.

    yes, i'm using top and also distinct clause inside the sql statement.

    Is there a workaround to order the view?

    Thanks for the reply

  • I have used the top(99.999) trick read here http://cf-bill.blogspot.com/2007/01/sql-server-order-view.html

    Since this view is not used by other views seems it works.

    Thank you.

  • evald (12/14/2010)


    bad design 🙁 At least sql has to inform me about this.

    You could read the docs. It is documented.

    yes, i'm using top and also distinct clause inside the sql statement.

    Is there a workaround to order the view?

    What's wrong with doing things properly - order by in the outermost query?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Please keep in mind that the "fix" you used will only work if the database is set to Compatibility 80.

    This means:

    a) You won't be able to use the new 2005 (or 2008) features in that database.

    b) When Danali comes out (SQL 11), it won't include Compatibility 80, so you'll NEVER be able to upgrade your server to a new version of SQL Server, unless you fix this so you are querying the view "correctly".

    If the business is happy with using an already-obsolete database engine for the rest of eternity, you're good to go. If, on the other hand, you ever want to use any features that have come out since 2005, you'll need to start querying the view with an Order By on the query, not in the view.

    Up to you. And your manager, of course.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GilaMonster (12/14/2010)

    You could read the docs. It is documented.

    Yes of course, but i mean if the dbms is accepting a sql statement i'm expecting that it works.

    This kinds of hidden "behaviour by design" are so confusing!!!

    GilaMonster (12/14/2010)

    What's wrong with doing things properly - order by in the outermost query?

    Well, i'm using this view in an application.

    If every instance of this application is requesting this view(with the order by clause), it means that the dbms had to order the results every time.

  • evald (12/14/2010)


    GilaMonster (12/14/2010)

    You could read the docs. It is documented.

    Yes of course, but i mean if the dbms is accepting a sql statement i'm expecting that it works.

    It does work. The outer query specifies no order, hence no order is required from the query engine. Orders within subqueries and views are only for when there's a row-limiting TOP clause

    This kinds of hidden "behaviour by design" are so confusing!!!

    By design everywhere, by ansi standard, by relational theory. It's also very easy to test and see how it behaves.

    Well, i'm using this view in an application.

    If every instance of this application is requesting this view(with the order by clause), it means that the dbms had to order the results every time.

    The DBMS will not order it. You will need to change the app and put the order by on the final query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster let me explain with a simple example because i'm not understanding you.

    Suppose the view is created like this(which is the same as mine except that i'm using also some inner joins):

    CREATE VIEW 'myview'

    AS

    SELECT DISTINCT TOP 100 PERCENT id, service, ordercolumn

    FROM sometable

    ORDER BY ordercolumn

    the above statement is accepted by Sqlserver. Sql server is not complaining about anything and I'm expecting that if i do "SELECT * FROM myview" i get the ordered result but this is not happening!

    This behaviour is not ansi sql, database theory or other, instead it seems to me that something is going wrong under the hood of sql server engine. This is my point of view on this behaviour.

    Now, correct me if i understood you wrong but you suggested to use in my application the statement "SELECT * FROM myview ORDER BY ordercolumn". The problem with this is that everytime my application is sending that statement to sql server, the last will always order the result instead if the "order by" works inside the view than sql engine will order only in case of insert,update or delete statements on the concerned tables.

  • evald (12/14/2010)


    GilaMonster let me explain with a simple example because i'm not understanding you.

    Suppose the view is created like this(which is the same as mine except that i'm using also some inner joins):

    CREATE VIEW 'myview'

    AS

    SELECT DISTINCT TOP 100 PERCENT id, service, ordercolumn

    FROM sometable

    ORDER BY ordercolumn

    the above statement is accepted by Sqlserver. Sql server is not complaining about anything and I'm expecting that if i do "SELECT * FROM myview" i get the ordered result but this is not happening!

    No, you should not get an ordered result. SQL expands out the query you wrote to

    SELECT * FROM

    (SELECT DISTINCT TOP 100 PERCENT id, service, ordercolumn

    FROM sometable

    ORDER BY ordercolumn)

    SQL then looks it this and makes some simplifications so that it can optimise the query better.

    The top 100 percent can go, because it does not limit rows.

    The order by is within a subquery that does not contain a row limitation (ie TOP) Therefore it is not needed and can also be removed

    So the result is this

    SELECT * FROM

    (SELECT DISTINCT id, service, ordercolumn

    FROM sometable)

    and that is what SQL executes

    This is my point of view on this behaviour.

    It may be your view, but it is incorrect. The only time SQL is guaranteed to return rows in the order specified by an order by is when that order by is on the outer select statement. So SELECT * FROM myView ORDER BY OrderColumn will always return rows ordered by OrderColumn

    This is clearly documented. From Books Online:

    When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

    http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.90%29.aspx

    Now, correct me if i understood you wrong but you suggested to use in my application the statement "SELECT * FROM myview ORDER BY ordercolumn".

    Absolutely correct. The order by needs to be in the outer select statement (the one submitted from the app)

    The problem with this is that everytime my application is sending that statement to sql server, the last will always order the result instead if the "order by" works inside the view than sql engine will order only in case of insert,update or delete statements on the concerned tables.

    Huh? Not sure I understand you.

    Views don't store data. They are only saved select statements. Tables have no defined order either. An order by on a view will have no effect at all on how data is stored in the tables that view depends upon. Order By is a clause in a select that orders rows returned by the query that it is part of. Nothing else.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'm also not sure how "Order By" would apply to a delete statement, unless you are deleting "delete top 10 from MyTable order by MyDateColumn" or something like that.

    Deletes are usually against specific rows, usually using either the primary key or some other specific row identifying feature.

    Same for Updates.

    And Order By only applies to Inserts when you want the input data to be in a specific order, not the target table. Again, not a common need, and seems to have no connection to what you're writing about here (an "ordered view").

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GilaMonster (12/14/2010)

    This is clearly documented. From Books Online:

    When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

    http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.90%29.aspx

    Yes, even bugs are well documented.

    Microsoft is saying: "Look man, for some obscure implementation reason of my engine i cannot guarantee you that the order by will work".

    It seems that Microsoft agree with me

    http://support.microsoft.com/kb/926292

    I'm going to verify if this hotfix is installed on my sql server and also i'll verify on a sql server 2008

    GilaMonster (12/14/2010)

    Views don't store data. They are only saved select statements. Tables have no defined order either. An order by on a view will have no effect at all on how data is stored in the tables that view depends upon. Order By is a clause in a select that orders rows returned by the query that it is part of. Nothing else.

    Yes, but i have noticed that it takes some times to "update" a view on sql server and i think that the only reason of this delay is that the view is somewhat cached in some place by the engine.

  • evald (12/14/2010)


    It seems that Microsoft agree with me

    http://support.microsoft.com/kb/926292

    I'm going to verify if this hotfix is installed on my sql server and also i'll verify on a sql server 2008

    Of specific note from that kb article:

    Actions after you apply this hotfix

    This hotfix introduces trace flag 168. After you apply this hotfix, you must enable trace flag 168. For information about how to enable a trace flag in SQL Server 2005, see the "Remark" section of the following Microsoft Developer Network (MSDN) Web site:

    http://msdn2.microsoft.com/en-us/library/ms188396.aspx (http://msdn2.microsoft.com/en-us/library/ms188396.aspx)

    Additionally, you must change the compatibility level of the database that contains the view to 80.

    To change the compatibility level, follow these steps:

    Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.

    Right-click the database that contains the view, and then click Properties.

    In the Select a page pane, click Options.

    In the Compatibility level list, select SQL Server 2000 (80).

    Note Trace flag 168 must be set before the database is migrated to SQL Server 2005. If trace flag 168 is set after the database is migrated, the query result will remain unsorted.

    It introduces a new traceflag, and requires it active before the migration. It also requires 8.0 compatibility, which is SQL 2k. They built the hotfix for migration so that you could have migrated your DB and not lose functionality until you fixed the code.

    TOP 100 PERCENT was a workaround for forcing a poor design methodology. A view is merely a subquery. The same as you can't write SELECT * FROM (SELECT * FROM tbl ORDER BY x,y), you can't (shouldn't, I guess, depending on version) order by in a view.

    It's not a matter of 'an unknown reason'. It's a matter of 'sort once, at the end of everything else you're doing, so you're not overworking the optimizer 15 times'. The internal optimizer is going to hash your data, it's going to re-sort it for inner loops, and it, in general, is going to mess with things. You order at the end, not in the middle. A view is supposed to be in the middle.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • evald (12/14/2010)


    Yes, even bugs are well documented.

    Microsoft is saying: "Look man, for some obscure implementation reason of my engine i cannot guarantee you that the order by will work".

    Absolutely not. This is by design and by intention. The optimiser intentionally ignores order by operations in views and subqueries unless there is a row-limiting top.

    It seems that Microsoft agree with me

    http://support.microsoft.com/kb/926292%5B/quote%5D

    In SQL 2000, the optimiser incorrectly honoured the order by in the view. People wrote buggy code that depended on that bug, then screamed blue murder when MS fixed the order by bug in SQL 2005. If you are writing code that depends on an order by working in a view, it's you that's writing the buggy code.

    Do note that you need to have that traceflag active before moving your database to SQL2005/2008. It's intended as a temporary measure so that people have time to fix their buggy code, not as a permanent measure.

    Yes, but i have noticed that it takes some times to "update" a view on sql server and i think that the only reason of this delay is that the view is somewhat cached in some place by the engine.

    They are not cached anywhere. A view is nothing more than a saved select statement, when you query a view the statement you send SQL is parsed, the view name replaced by the view definition and the resulting query optimised.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Read the following blog entry by Conor Cunningham (Principal Software Architect, SQL Server Engine at Microsoft):

    TOP 100 PERCENT...ORDER BY Considered Harmful

    That explains everything you need to know about this issue.

    For the moment, the following trick still works:

    CREATE VIEW myview

    AS

    SELECT TOP (9223372036854775807)

    id, service, ordercolumn

    FROM sometable

    ORDER BY

    ordercolumn;

    There's nothing to prevent SQL Server optimizing that trick away in a future version, either. I don't recommend this approach - just pointing out that it exists as a hack while you sort out your understanding of how SQL works, and how to design a database.

    Paul

Viewing 15 posts - 1 through 15 (of 62 total)

You must be logged in to reply to this topic. Login to reply