473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 14663
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Paul Shaw | last post by:
Can anyone explain why an insert might cause multiple logical bufferpool data reads? Here's a situation that has me scratching my head. Table A's data resides in tablespace B Table A's...
1
by: r1100r98 | last post by:
I am having a problem moving the data from a datatable to the SQL2005 table (using VB2005). See code below. The SQL2005 table is empty, the datatable is being filled from a text file, not from...
1
by: HandersonVA | last post by:
database has been recently upgraded from ms sql 2000 to ms sql 2005. are there anything I need to be aware after upgrading to ms sql 2005? for my experience, i got an error if i use column alias...
1
by: chencongwei | last post by:
Hi! This is what happened to me and my colleagues. We are using SQL 2005. When we were building a view from a table, the field we selected was not what came out in the output. Let's say you...
3
by: SuperJB | last post by:
Hi guys, I'm creating a personal project. One of things i'd like to do is store data of people, in particular - their picture. How do i insert images into a db (sql2005). In my table, i...
3
by: Jim | last post by:
I'm using framework 3.5, aspx vb and sql server 2005. I have a form that inserts user names. I don't want duplicate names, so how can I make a field be unique? Thanks.
3
by: Tawfiq | last post by:
Hi, We have shifted one of our data processing to a new instance of SQL 2005. For this a new DB was created in SQL2005 and it is not an upgrade of SQL200 DB. The data processing application...
3
by: Michael Schöller | last post by:
Hello, First of all english is not my natural language so please fogive me some bad mistakes in gramatic and use of some vocables :). I have a great problem here. Well I will not use it...
1
by: Beginnera | last post by:
I have never used php before however managed to install the script and setup the database tables for an open source script. So please excuse my lack of jargon. Currently a list of text ads that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.