Wednesday, June 14, 2006 3:20 PM
by
michael.k.campbell
ORDER BY and VIEWs in SS 2005
ORDER BY has never been technically 'allowed' in a view definition. For
example, the following code:
CREATE VIEW dbo.[UsersByFirstName]
AS
SELECT * FROM dbo.[users] ORDER BY [FirstName]
GO
will throw an error if you attempt to run it. Books Online for SQL Server
2000 says:
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.
Accordingly, people used to use the TOP 100 PERCENT hack -
something I never really liked because it typically ended up being seriously
abused with lots of nested queries and could cause some very gross performance
issues - but the HACK looked like so (and had pretty decent industry
acceptance):
CREATE VIEW dbo.[UsersByFirstName]
AS
SELECT TOP 100 PERCENT * FROM dbo.[users] ORDER BY [FirstName]
GO
The problem though, is that it was never technically really
guaranteed to work. Only it worked ALL the TIME in SQL Server 2000. But not with
SQL Server 2005. With SQL Server 2005 it actually makes the list of breaking
changes:
In SQL Server 2005, the
ORDER BY clause in a view definition is used only to determine the rows that
are returned by the TOP clause. The ORDER BY clause does not guarantee ordered
results when the view is queried, unless ORDER BY is also specified in the
query itself. [1]
I imagine some people might be bummed by that... but it's
really a non-issue. Technically speaking, ORDERING should be done on a query by
query basis - it's hard to argue that it's not intrinsically related to
presentation (the only argument would be if you were SELECTing the TOP n of
something - which will be honored by SQL Server as part of a VIEW... it's just
that presentation of the resultant rows will still the responsibility of the
outermost query [2] ).
References:
[1] - http://msdn2.microsoft.com/en-us/library/ms143179(SQL.90).aspx
[2]
- http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx