Sunday, November 9, 2008

MS SQL Views Don't Sort Data

If you're new to Microsoft SQL Server, one of the things that would bite you in the back is the ORDER BY clause within a view. SQL Server displays an error when you try to include an ORDER BY clause within the SQL statement used to define the view, but you can get around the warning by using the TOP 100 PERCENT clause. This, however, does not force SQL Server (2008) to order the data - the ORDER BY clause is simply used to determine which rows will be returned in the dataset.

It's probably Microsoft's way to put an end to developers' abuse of the sort functionality - sort the data once from a view, again in a subquery and finally in the select statement returning the final data.

2 comments:

marc said...

You can select top 99 % to get a sorted view and bypass the MS block. They specifically block top 100% but allow top 99% to return sorted data.

HungBM06 said...

@marc: your solution is the best :)
This work perfect!Thanks man!