General discussion


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

By micdev42 ·
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
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
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

It's a curious feature...

by EricAlter In reply to Why would you use SELECT ...

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?...

Collapse -


by Jaqui In reply to It's a curious feature...

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 ]

Collapse -

TOP allows ORDER BY in a view

by Etaoin Shrdlu In reply to It's a curious feature...

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

Collapse -

Why Top 100 Percent?

by artful In reply to It's a curious feature...

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.

Collapse -

Unsure why you can't find me

by artful In reply to Why would you use SELECT ...

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 and attempt to stitch the wound, whatever it may be.

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


Related Discussions

Related Forums