order by in a view in sqlserver2005

order by in a view in sqlserver2005

• DonnyK

  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.

  
  
  

• sogod

  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.

  
  

• Danny Fay

  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:

  http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

  
  
  

• DotNetFireball

  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.

  
  

• MP0401

  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.

  
  

• Richard Grant

  Now you've nailed it. This behavior, which unfortunately
  persists in SQL Server 2005 Enterprise Manager's Query and
  View Designer, is idiotic. A good place to add your
  voice to the chorus is at the Microsoft Product Feeback
  center, where you can vote on the at least three
  bugs/suggestions about this behavior (URL may wrap).
  
  http://lab.msdn.microsoft.com/productfeedback/SearchResults.aspx text=view+%26quot%3border+by%26quot%3b+percent&stype=1&fields=1&type=0&witId=0&pId=0&category=0&os=0&oslang=0&status=0&msstatus=0&resolution=0&chgdays=&validation=0&votes=&voterating=0&workarounds=False&attachments=False
  
  SK
  
  Brent Mullet@discussions.microsoft.com wrote:
  > 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.
  > 
  

  
  

• CJ_

  Brent,
  
  I understand how this behavior is inconvenient, but
  it is consistent with ANSI/ISO standards, and it is
  consistent with Microsoft SQL Server documentation,
  which has recently been made clearer about this:
  
  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
  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.
  
  In my opinion, the confusion is caused by the syntax
  Microsoft chose for its proprietary TOP clause. Instead
  of using ORDER BY, which already has a meaning in standard
  SQL, to specify the ordering used by TOP (thereby adding
  a second meaning to ORDER BY), the syntax should have been
  something like
  
  CREATE VIEW V AS
    SELECT TOP (10) OVER (ORDER BY someColumn) *
    FROM T
  
  Then an ORDER BY clause (at the end of the query statement)
  could have been prohibited completely from view definitions,
  and there would be no question about this. But unfortunately,
  that's not the way T-SQL is, and there is a lot of misunderstanding.
  
  In any case, the only way to guarantee a result set's ordering
  is to specify ORDER BY in the query that returns the result set.
  The view definition is not the query that returns the result set,
  so ORDER BY in the view definition for V does not control the
  order of the result set generated by SELECT * FROM V.
  
  The workaround you suggest may seem to solve the problem, but
  you should be aware that it is not guaranteed to work. Unless
  you want to risk going through this trouble again in the future
  (perhaps after a service pack that introduces new optimizer
  enhancements), you should specify an ORDER BY clause every time
  you want an ordered result set. You are not "re"specifying the
  ORDER BY clause, since the one in the view definition is not
  a specification for the order of the results - it is a specification
  (that works with TOP) of what rows the view contains, regardless of
  the order in which you might want to retrieve them.
  
  Steve Kass
  Drew University
  
  Brent Mullet@discussions.microsoft.com wrote:
  > 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.
  > 
  > 
  > 
  > 
  > 
  > 
  

  
  

• himo

  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

  
  

  
  

• Thomas82

  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.
  

• Manjunath.H.A

  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.

  
  
  
• order by in a view in sqlserver2005