locked
order by in a view in sqlserver2005 RRS feed

  • Question

  • Hi,

    I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.

    This is the view :

    CREATE VIEW hsarti01_VD1 AS
    SELECT TOP 100 PERCENT *
    FROM hsarti01 WITH(index(hsarti01_PK))
    ORDER BY 1,2 desc,3,4

    When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !

     

    Has anyone have a solution for it ?

    Thanks

    Saturday, February 11, 2006 10:05 AM

Answers

  • Order of rows in a result is guaranteed only if you specify an ORDER BY clause in the outer-most SELECT statement. Anywhere else the optimizer is free to remove it or preserve order only within that scope. So you will be relying on a particular plan behavior and the expected output will change if the plan changes. This can happen between service packs or releases or hotfixes. It is hard to tell. The following warning has been added to the CREATE VIEW topic in SQL Server 2005 to reflect the correct behavior:
     
    The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
     
    So modify the SELECT that queries the view to include appropriate ORDER BY clause. This is the only sure way. Please see the link below for additional information on ordering guarantees in SQL Server:
     
    Saturday, February 11, 2006 6:10 PM

All replies

  • Hi I have found out that the percent directive gives this problem.
    I guess it is a bad solution but replacing this with a ridiculus high number solves the problem say

    Alter VIEW hsarti01_VD1 AS
    SELECT TOP 10000000000 *
    FROM hsarti01 WITH(index(hsarti01_PK))
    ORDER BY 1,2 desc,3,4

    solves this. I guess however that ordering in views is not a realy a good thing, emagine you select on the view with an order statment, wais a few cpu cycles

     

    Walter


     

     

    Saturday, February 11, 2006 11:34 AM
  • Order of rows in a result is guaranteed only if you specify an ORDER BY clause in the outer-most SELECT statement. Anywhere else the optimizer is free to remove it or preserve order only within that scope. So you will be relying on a particular plan behavior and the expected output will change if the plan changes. This can happen between service packs or releases or hotfixes. It is hard to tell. The following warning has been added to the CREATE VIEW topic in SQL Server 2005 to reflect the correct behavior:
     
    The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
     
    So modify the SELECT that queries the view to include appropriate ORDER BY clause. This is the only sure way. Please see the link below for additional information on ordering guarantees in SQL Server:
     
    Saturday, February 11, 2006 6:10 PM
  • I think this is a horrible *BUG*.  I think if an Order By is specified in the view, than the results should ALWAYS be returned in that order unless an outer query is used to resort the view (just as you can use Where to further filter results). Otherwise you constantly have to re-specify the order of the view everywhere it is used.  This greatly reduces the value of using the view.  With SQL 2000, I could simply open a view in my application and navigate through it, confident that the records were in the correct order.  Now with 2005 I have to RESPECIFY the order by statement EVERYWHERE the view is used.  This has introduced a number of logical errors in my application, and I think it was a horrible oversight and bug.  You can try to call it a "feature" but that's garbage.

     

     

    Monday, May 1, 2006 5:47 PM
  • I still think it's stupid and makes no sense.  You specify SELECT, WHERE, and GROUP BY statements in views and those are all respected but ORDER BY is not.  There is no reason for this.  If I want ordered results, I should be able to specify it in a view and be confident that wherever the view is used the Order By is respected.  This is a basic programming principle.  If the same view is used in many places in an application, and for some reason the order needs to change, I should be able to do that globally just as I can with WHERE.  This is a BUG that needs FIXED.

    If it's specified that way in the ANSI/ISO standards, than those standards need fixed.  This is ludicrous.

    Monday, May 1, 2006 7:25 PM
  • A prime problem is that what happens when you joint this "ordered view" with another "ordered view"?  Who comes first?  

    A view is a table, which is by definition, unordered. The SELECT, WHERE, and GROUP BY clauses shape the data in the table, but the order is not a part of a table.  To change that you would have to change the root of the theory that relational databases have been built upon for years.  Not to mention the definition of a SET would need to be changed and all of the optimizers of database servers rethought, since this table's order could affect the users of the table. 

    If you want the data to be consistently returned in an order via code, it is best use a stored procedure.

    Monday, May 1, 2006 8:28 PM
  • Ahah!  Now at least I think it makes a little more sense.  I did not think about using stored procedues; I've been using views for a long time, and that worked fine in 2000.  So part of the issue was my ignorance (blush).  Converting to stored procedures is a perfectly acceptable solution. (And there are probably other benefits to that as well?)

    So it's no longer a STUPID BUG it's a DANGEROUS TRAP that IGNORANT USERS can fall into when they UPGRADE

    If the ORDER BY is not respected in it's intuitive sense, I think it should not be specifyable unless it is somehow tied directly to the TOP statement.

    Thanks for the response Louis.

     

     

    Monday, May 1, 2006 8:57 PM
  • This is currently the case.  Try to build a view with an order by any you get a nasty message:

    create view test
    as
    select *
    from sysobjects
    order by 1

    In 2000:
    Msg 1033, Level 15, State 1, Procedure test, Line 5
    The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

    In 2005:
    Msg 1033, Level 15, State 1, Procedure test, Line 5
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    I know how you feel though.  I had used this in my views in past versions, (as well as the fact that clustered data was naturally returned in clustered order) and for the most part it still holds true, especially in testing, because it is usually easier for to return the data in order because if the query processor needed it in order to determine the TOP rows, it is unlikely to be faster to reorder.  But as the query optimizer/processor gets more and more sophisicated, the more likely it is that they will find ways to maximize output and lose the ordering in the process. 

    In all cases it is better to either use ORDER BY, or just let the client sort the data.

    Monday, May 1, 2006 9:07 PM
  • Yes but creating a new view in Enterprise Manager defaults to "TOP (100) PERCENT" - and when this is present, the Order By is accepted.  In this case, the Order By is meaningless and dangerously misleading.
    Monday, May 1, 2006 9:20 PM
  • The thing is Order By always comes at last. If you wrote like this:

    select TOP 1 * from
        (SELECT * from tableA
    Order By tableA.ColA
    ) as refTable

    You will have an error. However, if you take Order By at last, it works:

    select TOP 1 * from
        (SELECT * from tableA

    ) as refTable
    Order By refTable.ColA



    Monday, January 25, 2010 10:56 PM
  • The problem with the first query is not that ORDER BY has to be last, there is no such syntax requirement. You will get an error in the first query because ORDER BY cannot be used in table expression (since then it is not really a table expression but a cursor) without the TOP option (the TOP option consumes ORDER BY and you end up with table expression that has no order). This is a perfectly valid query:

    SELECT TOP 1 ColA
    FROM (
    SELECT TOP 50 ColA
    FROM tableA
    ORDER BY ColA
    ) AS refTable;

    Of course, the main query has no ORDER BY clause and that will result in non-deterministic results as TOP 1 will not be based on any order.
    Plamen Ratchev
    Monday, January 25, 2010 11:46 PM
  • Interestingly, I saw a discussion on another forum today related to this question
    http://forum.lessthandot.com/viewtopic.php?f=17&t=9691&start=0
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, January 26, 2010 1:08 AM
  • Executing View

     

     

    CREATE VIEW [dbo].[vw_AS_SoAndSo] AS

    SELECT

    strDescription

    FROM tblThisOrThat

    ORDER BY intPresentationSequence ASC
    GO

     

    generated error:

    “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.”

     

    I counteract this error by

     

    CREATE VIEW [dbo].[vw_AS_SoAndSo] AS

    SELECT

    TOP (SELECT COUNT(*) FROM tblThisOrThat ) strDescription
    FROM tblThisOrThat
    ORDER BY intPresentationSequence ASC

    GO


    Success!!

    Thursday, June 10, 2010 9:58 AM
  • @New Limit User

    This is not an error; there are very good reasons for not putting ORDER BY in views, which is why such functionality is rightly disabled in SQL Server.
    Re-read the answer to this thread aalong with the link that's in that reply.

    You cannot and should not expect order without specifying in the outer-most select.


    George
    blog | twitter
    Thursday, June 10, 2010 10:24 AM
    Answerer