Posted by David Portas on 02/07/06 17:43

Ian Bell wrote:

> Hi All
>
> I have just upgraded to SQL2005 and found an annoying problem. Whenever
> I add a new view or amend an existing one (created in SQL 2000
> Enterprise Manager) it inserts TOP 100 Percent into the SQL query. Even
> if I delete the clause from the SQL query it puts it straight back in
> again. This is a problem as there are some queries where I just don't
> want this clause there (ADO.NET cannot update a view with a TOP clause)
>
> Any ideas anyone?
>
> Many thanks
>
> Ian Bell
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

SQL Server 2000 Enterprise Manager does the same if you select anything
in the Sort Order column in the Design grid. Until now I hadn't noticed
that feature was still enabled. That's pretty dumb because it may
mislead users into thinking that the view is somehow ordered when
actually it isn't. Maybe they left it in because they anticipated lots
of anguished support callers demanding "where did the order by go?".
Wrong decision in my view.

Do you really like to use the Designer to create views and queries? I
don't and I don't know anyone else who does. The best way to create a
view is to use CREATE VIEW in a query window. No TOP 100 PERCENT in
sight.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]