469,275 Members | 1,749 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Why does SQL2005 automatically insert TOP 100 PERCENT in views?


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 ***
Feb 7 '06 #1
3 14466
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
--

Feb 7 '06 #2
Another evil trick to lock you into MS proprietary code! :)

Feb 7 '06 #3
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

Feb 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Paul Shaw | last post: by
1 post views Thread by HandersonVA | last post: by
1 post views Thread by chencongwei | last post: by
3 posts views Thread by Jim | last post: by
3 posts views Thread by Tawfiq | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.