September 8, 2008 at 8:26 am
Hi,
I cannot use ORDER BY clause in view unless TOP is specified. Without using top I get following error,
Msg 1033, Level 15, State 1, Procedure , Line 4
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.
Any idea why top is required in views, inline functions, derived tables, subqueries, and common table expressions for ORDER BY cluase?
Regards - JL
September 8, 2008 at 8:31 am
Because an order by is only valid in the outermost select statement. It can be used within other blocks, but then must be a part of a TOP n, where n is not 100%.
You can specify TOP 100%... ORDER BY in a view, but when you select from the view, SQL will ignore that order by. If you need the data ordered, the order by must be applied to the select statement that queries from the view.
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
September 8, 2008 at 8:56 am
For the same reason you don't have Order By in a table (clustered index isn't the same as Order By).
Like Gail said, it's because those objects exist to be queried. The Order By should go in the query, not in the object itself, unless the object is going to limit the number of rows it returns by using Top and Order By.
The reason you can have Order By in procs without Top is because you don't normally query a proc. You execute it.
It's a "what is this thing for anyway" rule.
- 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
September 8, 2008 at 4:55 pm
This reminds me of a discussion I had in this board a while ago.
In the end this is the rationale behind it:
SQL Server engine is working with SETs. The SETs have no ORDER per definition.
There is a performance benefit for the SQL Server Engine when does not have to maintain the order of intermediate results.
The following two valid scenarios exist for ordering:
1. You want to return ordered data to the client. In this case it makes sense to order the data as the last step. Sorting the data earlier does not provide any benefit.
2. You want to work on a subset of data like "Top 5 Selling Cars". In this case, the ORDER BY makes sense. You select the top 5 rows after they have been ordered by the sales metric in descending order.
Other than that, there are no "logical" uses of an ORDER BY.
When I originally posted the question, my wish was to use the ORDER BY within views to have a default sorting. Since I only returned the data from these views without any further operations, the ORDER BY was actually the "last step" in the queries, but SQL Server does not know this.
Back then, I was "suggesting" to let SQL Server allow ORDER BY within views, but do not allow these views to be referenced by other objects within the DB.
But in the end, the proper way is to just avoid ORDER BY as suggested by BOL.
Best Regards,
Chris Büttner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply