ORDER BY clause within a view does not work in SQL 2005
-
I have come across a strange behaviour in views and would like to know if this is documented somewhere. If a view in SQL 2005 has an order by clause in it itself and you issue a select * from view then the order by is effectively ignored. In SQL 2000 the same command uses the order by clause.
TIA
Neil.
-
I thought ORDER BY clause's are not allowed in view creation and this has been this way for ages. You select the ORDER BY when selecting from the view and the plan will be different
-
Yep On defining a view it throws the following
Msg 1033, Level 15, State 1, Procedure T1, Line 1
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.
-
You can create a view like this
create view vwX as
Select top 100 percent * from tblA order by X desc
This is the way a SQL 2000 view that I have inherited was created and the application performs a Select * from vwX
I cannot easily change the application, but when testing in SQL 2005, the order in the view does not work anymore.
thanks
Neil.
-
Hello Neil,
The situation you describe was explained in the June issue of SQL Server Magazine. You can see it here:
-
I guess the confusion starts because SQL Server 2000 would return ordered data and SQL 2005 doesn't.
The same happened to me when a procedure stopped working properly after an upgrade to 2005. That had us thinking for sometime, but we realize the current behavior is the correct one.
-
Exactly, as I said, this is a 'view' that I have inherited and cannot change the calling application, so when testing an upgrade to sql 2005 we received different results becuase the originial developer put the order by in the view. I know that this is not right and I will investigate changing the application, I was just not expecting the different functionality, especially as SS2005 still lets you have a view with an order by clause so I guess that is an MS fault as it should prevent it from being saved.
-
If you are selecting less than 100 percent of the records returned in the select, the order by in a view works. SQL 2005 ignores (as it is written in posts and links above) if you are selecting 100 percent of the query.
-
You can't use order by statements in a view ( not sure if this is part of ANSI standards but I think so ), a bug within sql 2000 allowed a top 100% with an order by to work, this doesn't work in 2005 and was very clearly stated numerous times by Microsoft.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Not disputing that using the order by in a view defined with a select top 100 percent doesn't work in SQL Server 2005. I am simply stating that it isn't error as you CAN use the order by in a view defined with a select top N percent where N < 100, as it does work in that case in SQL Server 2005.
-
We have just had exactly the same problem as Grasshopper.
I don't understand why various writers have said you can't use ORDER BY in views. If you create a view in SQL 2000 or 2005 using EM and add an ORDER BY clause, EM automatically adds a TOP 100 PERCENT. This implies to me that the feature is supported in views. What's more if you specify ANY value less than 100 it works.
I'm no RDMS expert but I have a need for ordered views and the idea that I shouldn't be allowed to have them seems to me the theory getting in the way of practicality.
By the way TOP 99.9999 PERCENT works and as the tables I'm working on have less than 10000 records that's good enough for me.
-
The standard behavior here is that an order by clause is only required to be honored by SQL Server _for the outermost select statement in a query_ or in cases where it would have a logical impact on the results (such as using a derived table in a query for ranking). I got a little burned by this myself. Ultimately it means that for a view and/or SQL Server table-valued function, any ordering MUST be specified client-side, as the client is the place where the outermost select is defined, and the other conditions won't apply. This was previously just an oft-bended rule; now it's a requirement.
The SQL Server 2000 "select top 100 percent ... order by" in views and TVFs was an anomaly, which has been removed -- and that removal was publicized.
However, this is one example of the collision of theory and practice (IMHO) because while a table or view is defined as an unordered set, in the real world of applications people practically always want ordered results in their client app. The perceived need for "select top 100 percent" and its widespread use is a symptom of that. People figured out that they could do that because, well, they needed ordered results and it was more convenient than putting the ordering in the client app, no matter what the standard or theory said. Now you have to put it client side.
-
This was very helpful. Has anyone experimented with the percent clause?
I tried "select top 99.999 percent" and was able to get the result set I wanted - and it was ordered. Of course, there is always the possibility of missing 1 row of data.
-CN
-
That will work for now, but it is only a temporary solution. Future releases might fix the posibility of selecting top < 100 percent and that would break your application again. The correct way of addressing this problem is ordering after the fact.
MHO, of course.
-
To save multiple postings have a look at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=392573
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply