TOP n PERCENT usage in Views

Question

TOP n PERCENT usage in Views

Mark Taylor-212571

Ten Centuries

Points: 1196
More actions
November 10, 2005 at 6:40 am

#169731

Help...
I was recently asked why you need to use the TOP n PERCENT when you want to use ORDER BY in a View. I have read trough BOL and had a look round the old internet but cannot find any articals on this subject.
So, can anyone advise as to the reason why you need to us the TOP n PERCENT?
Regards
Mark Taylor

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You only need to use it with the order by clause. It has something to do with getting the best execution plan possible... but I can't elaborate much more than that.

Mark Taylor-212571 Ten Centuries Points: 1196 More actions · Answer 2

Thanks for looking into but need a more definitive answer, cheers.

Sergiy SSC Guru Points: 110206 More actions · Answer 3

Why there must be a dot at the end of any sentence?

Why you must start a sentence with a capital letter?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

First, I'd have to ask why do you need a more definitive answer? The simple truth of the matter is that's the way they made it and none of us can change it.

Now, for the more definitive answer...

"TOP" in a view is not the problem... it's the Order By... it doesn't make sense to use the extremely resource intensive Order By clause in a view unless you want the top lowest or highest of something. And, you could always do an Order By in the query that selected from the view. When SQL Server was young and servers were a lot slower, Microsoft made the requirement of including TOP with ORDER BY in views and subqueries to, well..., keep people from doing stupid things that would make their product look bad. So why allow it with TOP 100 Percent? I guess Microsoft decided that they could make things gumby proof but not idiot proof. If you really, really wanted an Order By in a view, they wanted you to have to jump through a hoop to remind you that it really, really wasn't a good idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vinny_1973 SSCommitted Points: 1782 More actions · Answer 5

it doesn't make sense to use and ORDER BY in a view, period! It's supposed to be a subset of the data ... but i suspect that it's being used insead of SP's to pre-define queries! Very bad practice that i've seen a million times before!

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

I absolutely agree... especially when they nest such views...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)