Sunday, July 15, 2007

MS SQL Server Views: Order By

If you create a view in SQL Server 2005 management studio and add an order by clause, run the query, the order by seems to take effect.
But when you reference the view in a simple query (with no order by) you find that the returned results are not as expected, records are not ordered..
check the definition of the created view :

sp_helptext view_name

you find that the management studio added a TOP clause to the view.
reviewing SQL Server Books Online:

SQL Server 2000:
The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.

SQL Server 2005:
The SELECT clauses in a view definition cannot include the following:
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

This makes extreme sense to me, as views represent logical tables, so just like tables, the order in which you get data should not be assumed, unless explicitly requested using an order by in the query.

No comments: