order by in a view in sqlserver2005

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

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

|||

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

|||

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.

|||

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.
>
>
>
>
>
>

|||

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.

|||

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.

|||

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.

|||

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.

|||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.|||

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.
>