Discussion Locked

Why would you use SELECT TOP 100 PERCENT in a SQL Server 2000 query?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Sorry if this is a bit basic, but I've just seen Arthur Fuller's 'Alter every table in a database' email article which I've just been sent via SQL Server Tips at Builder.com.
He uses the above idiom to return table names. What not just use SELECT x from table? Am I missing something?

BTW, I would have asked him the question directly but, by searching builder.com...
1. I can't find this article online.
2. I can't find a reference to Arthur.

Answers to any of the above would be appreciated. Thanks.
Michael

5 replies

by ericalter

From my understanding, there would be no benefit from using, or not using, the TOP 100 PERCENT clause in a Select query. However, if you've ever worked with the Query Designer in the Views pane, you may know that whenever you save a new view, SQL Server graciously adds the clause into the SQL for you, at no extra charge. So, if you're in the habit of copying and pasting SQL code after using the Query Designer to create it, you might find it less of a nuisance to just leave it in. It's a curious "feature" of SQL Server. Perhaps there's a programmer out there who knows of a more interesting purpose?...

by jaqui

it's not in the sql standard, so it is a bug.
not a feature.

[ insert rabid anti ms comment here ]
[ inster rabid anti ms programming habits comment here ]

by etaoin shrdlu

I believe if you want to create a view that orders the results, you have to use TOP, e.g. TOP 100 PERCENT

by artful

Quite right! It's automatically added, or in complex cases you may find that you must add it yourself. The cool thing about it is that it allows you to Order By. Without this construct, you cannot order a view. With it, you can.

by artful

Thanks for the questions. I will attend to them momentarily. The more disturbing part is your inability to reach me. I will take that up with builder.com and attempt to stitch the wound, whatever it may be.

In the interim, you can always reach me directly at artful@rogers.com. Please preface your subject line with something like TechRepublic or Builder, so I know from whence the message came.

Arthur

This discussion is closed to new replies.