Top and Order By Combination

Top and Order By Combination

By : Kasim Wirama, MCDBA, MCITP

 

Given that a table (for example : content table) contains integer records : 2, 1, 3 and I would like to retrieve the rows in ordered fashion, you simply issue a query : SELECT COL1 FROM CONTENT ORDER BY COL1. It is simple, isn’t it?

Now, next requirement that you are required to put the query into view. The following view creation here :

create view dbo.OrderedContent
as
select  cola
from content
order by cola;

will give error :

“The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.”

So you need to put TOP 100 PERCENT expression in column list so the view creation becomes :

create view dbo.OrderedContent
as
select  TOP 100 PERCENT cola
from content
order by cola;

Now your view has been successfully created. If you use SQL Server 2000, this query statement will return ordered result as expected.

SELECT * FROM dbo.OrderedContent

Now you upgrade from SQL Server 2000 to SQL Server 2005 or SQL Server 2008 you will get unordered result.

This is not bug because actually View object is a table expression; and if you specify ORDER BY clause, the result would not be a table expression but ANSI SQL cursor statement. SQL Server 2005 and later version put View objects as pure table expression so that they will ignore TOP…ORDER BY  combination in View.

Mitigate this potential problem if you want your database is ready for upgrade process.

Share this post: | | | |
Published Thursday, December 25, 2008 6:30 PM by Kasim.Wirama
Filed under:

Comments

No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems