Using ORDER BY in a VIEW: SQL 2005 vs. SQL 2000
-
I've just learned that the ORDER BY clause in the SELECT statement of a VIEW no longer sorts the rows returned by the view.
BOL 2000 says simply that you cannot "Include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement."
BOL for 2005 says the same thing but includes this note: "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."
I'm guessing this is new behavior for SQL 2005, becuase I discovered the difference after migrating a db from SQL 2000 to SQL 2005, and I've tested on both platforms to confirm the difference. For the app I'm migrating we have a bunch of "sorted" views, for example:
CREATE VIEW dbo.vwDepts
AS
SELECT TOP 100 PERCENT DepartmentDesc
FROM dbo.tblDeptartments
ORDER BY DepartmentDesc
So my question is basically this: is there something I can do to SQL Server to get such a view to return sorted rows? or do I have to bite the bullet and edit all the application code that references such views to include an ORDER BY clause?
Other info:
The DB in question is still in SQL 2000 compatibility mode on the 2005 server.
I've tried getting tricky by selecting TOP 99 PERCENT instead of TOP 100 PERCENT, and that does return sorted rows, but besides being a cheap hack, it only works on tables with less than 100 rows.
Any ideas or advice are appreciated. Thanks,
--MIJ
-
You will have to bite the bullet. If you do a search of this site, you will find several threads that discuss this very issue.
-
Lynn, thanks for the clue. I don't know what I was doing the first time I searched, because I found a lot more relevant results when I searched again later. (
for redundant post)For those who may still be curious (if I can answer my own question): the cheap hack mentioned in my original post can be extended for larger tables, but it's probably a very bad idea in general (subsequent sorts and joins hurt performance), just as it probably wasn't a very good idea to sort within a view to begin with (...I was wondering why the original developer did that).
-
Does this mean that, after you've placed the 'order by' clause in all the app code, you can remove it from the view, so there's no ambiguity? just a thought...
-
It depends. If the select in the view is written SELECT TOP 100 PERCENT ..., then yes. If it actually returns a small subset from the target table(s), then I would say no. You would need to follow the age old paradigm: test, test and test again to be sure.
-
ORDER BY should never be in a view definition.
a view returns a set, and a sets are unordered.
if you want the result to be ordered, put ORDER BY in the query that targets the view (which ought to be in a proc, not in client code!)
The optimzer reserves the right to ignore the order by in a view definition in the search for more efficient query plans.
for details see here: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
---------------------------------------
elsasoft.org -
Never say never. If the view only returns, say the TOP 10 PERCENT, the ORDER BY is used to determine which top 10 percent is returned. Again, test, test, and test again to be sure you are getting what you expect.
-
Still, in which order those 10 percent rows are finally presented to the client are not guranteed to be the same.
/Kenneth
-
True.
-
agreed. wasn't thinking of that case.
usually what you see is the TOP 100 PERCENT hack which is totally indefensible.
---------------------------------------
elsasoft.org
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply