Saturday, 25 November 2006

Order by clause is no longer supported in SQL Server 2005 Views

We used to have an application running on Sql Server 2000. And we built a view in which we put a Order By clause to pull data out in order. This works fine and the data is ordered. When we migrate the database to Sql Server 2005, we found the view is no longer ordered even the order by clause is still there. After some research on the net, we found out the Order By clause in a view has never been documented anywhere and this is not supported in Sql Server 2005 anymore.

To make an experiment, I created a view in Sql Server 2000 and 2005 as below:

Create View EmployeeView AS
SELECT TOP (100) PERCENT EmployeeID, LastName, FirstName
FROM dbo.Employees
ORDER BY LastName

When I run a query as Select * from EmployeeView against Sql Server 2000, I get the correct result with LastName ordered. When I run the same query against Sql Server 2005, the result is in the same order in the Employees table, not ordered by the LastName.

No comments: