SEO

#1 (**permalink**) 02-29-2008, 06:19 PM

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 ***

#2 (**permalink**) 02-29-2008, 06:20 PM

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/m...S,SQL.90).aspx
--

#3 (**permalink**) 02-29-2008, 06:20 PM

Another evil trick to lock you into MS proprietary code!

#4 (**permalink**) 02-29-2008, 06:20 PM

Hello, Ian

The ORDER BY clause cannot be used in a view if the TOP clause is not
specified. However, if we specify TOP 100 PERCENT and ORDER BY in a
view, this does not guarantee that the result of a "SELECT * FROM
TheView" will be ordered. Incidentally, it was ordered in SQL Server
2000, but it is not ordered in SQL Server 2005. It was never guaranteed
to work, it was just a side-effect that it worked in SQL Server 2000.
The ORDER BY clause in a view is taken into consideration only if the
TOP clause contains something other than "100 PERCENT", for example
"TOP 5" or "TOP 10 PERCENT".

Conclusion: You should not use ORDER BY in a view. You should put the
ORDER BY clause in the SELECT query that uses the view, for example
"SELECT * FROM TheView ORDER BY SomeColumn".

Razvan

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Why does SQL2005 automatically insert TOP 100 PERCENT in views?