November 10, 2005 at 6:40 am
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
November 10, 2005 at 7:58 am
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.
November 10, 2005 at 9:12 am
Thanks for looking into but need a more definitive answer, cheers.
November 10, 2005 at 3:49 pm
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
November 10, 2005 at 10:11 pm
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
November 11, 2005 at 4:34 am
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!
November 12, 2005 at 10:14 pm
I absolutely agree... especially when they nest such views...
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply