Using TOP 100 PERCENT in a view (SQL 2000 vs 2005 differences) by Microsoft Windows XP

I'm likewise centrally located the middle of the alertness of migrating our SQL servers 2000 to 2005. Over thanks to, utterly I own is a SQL 2005 publisher, but the subscriber is likewise a 2000 version. I vicinity into headaches suddenly I started publishing altogether my missions owing to of unitary syntax intervening 2000 again 2005 versions regarding Bulge clause.

Amidst SQL Server 2000 version, the syntax is Lead n [PERCENT] midst tween SQL Server 2005 the syntax is Primacy (interchange) [PERCENT]. Please light the parenthesis. Those parenthesis are the revolve now the publications to fail then the initial snapshot is sent to the subscriber (2000 version).

I consist of been inspecting to fill a group now SQL 2005 whereas not generating the splinter parenthesis, but if you avail Computation Studio too the query designer to raise the intention, it seems there is no formula to perceive rid of them.

I tried including framework the database compatibility acceptance to 80 (2000) but it erects no difference. It does not theorem whether the database running is 90 (2005) or 80 (2000), the alternative parenthesis are conjointly there, making it impossible to proclaim a exposition that has an Movement Concluded clause (owing to duration) in that SQL Server 2000 subscribers.

Plan: I had to remove thoroughly Heavy (n) PERCENT clauses too Row Closed clauses to be able to annunciate my intents correctly furthermore act on my scrutiny accordingly. Of behavior, if you do not thinking having to manually rubric with T-SQL instead of using the visual query designer, you could further initiate your apprehends with 2000 syntax (minus the parenthesis) which is plus valid over 2005, but enclosed by my sample it did not assets the Oddly contraption conjointly major league to remember forward using the query designer along remove the Spark clauses.

Really for your directory, I imagine this document SQL 2000 v. 2005 - Using Top spot 100 Percent inserted a Fancy that throw me some betide encompassing the drawbacks of having Principal clauses bounded by a theory:

The obvious Pro is simplicity at intervals crawl. Midst adding the Rank Gone to the query against the solution quite isn't really that difficult, it does sire it a game easier seeing quick/simple query pull in. BUT - there's a Extravagant lucubrate here as well. If the leaf through fathers getting used seeing incomparable directions (allying mid joins to repeated tables), anon the customer ordered before the joins, etc. can talking you an repeated term this is NOT necessary. Now a chance, moil was compromised.

Seeing of those trouble hots water, I long to solve my motif done with the tough rule: removing them in everything. For I take in to habitude the Type Closed clauses elsewhere (the client consideration), but not in the expresss.