2006/08/25

Using TOP 100 PERCENT in a view (SQL 2000 vs 2005 differences)

I'm still in the middle of the process of migrating our SQL servers 2000 to 2005. By now, all I have is a SQL 2005 publisher, but the subscriber is still a 2000 version. I run into problems when I started publishing all my views because of different syntax between 2000 and 2005 versions regarding TOP clause.

In SQL Server 2000 version, the syntax is TOP n [PERCENT] while in SQL Server 2005 the syntax is TOP (expression) [PERCENT]. Please note the parenthesis.

Those parenthesis are the reason for the publications to fail when the initial snapshot is sent to the subscriber (2000 version). I have been trying to find a way for SQL 2005 for not generating the extra parenthesis, but if you use Management Studio and the query designer to create the view, it seems there is no way to get rid of them. I tried also setting the database compatibility mode to 80 (2000) but it makes no difference. It does not matter whether the database mode is 90 (2005) or 80 (2000), the extra parenthesis are still there, making it impossible to publish a view that has an ORDER BY clause (for instance) for SQL Server 2000 subscribers.

Solution: I had to remove all TOP (n) PERCENT clauses and ORDER BY clauses to be able to publish my views correctly and modify my application accordingly. Of course, if you do not mind having to manually handle with T-SQL instead of using the visual query designer, you could still create your views with 2000 syntax (without the parenthesis) which is still valid for 2005, but in my case it did not worth the extra job and decided to keep on using the query designer and remove the TOP clauses.

Just for your information, I found this document SQL 2000 v. 2005 - Using Top 100 Percent in a View that throw me some light about the drawbacks of having TOP clauses in a view:

The obvious Pro is simplicity in access. While adding the ORDER BY to the query against the view really isn't all that difficult, it does make it a bit easier for quick/simple query access. BUT - there's a HUGE con here too. If the view starts getting used for other purposes (like in joins to other tables), then the being ordered before the joins, etc. can cause you an additional step that is NOT necessary. As a result, performance was compromised.

Because of those performance problems, I decided to solve my problem by the tough way: removing them completely. Now I have to use the ORDER BY clauses elsewhere (the client application), but not in the views.

1 comment:

itsthesource said...

There is another alternative. Generate an alter script for the 2005 view and remove the parentheses. Execute the script & then publications to SQL Server 2000 do not fail.