SELECT TOP 100 PERCENT IN CREATE VIEW
-
I saw a lot views created by some contractors that it all had
CREATE VIEW vw_test
AS
SELECT TOP 100 PERCENT
....
FROM Table
WHERE
But there is no ORDER BY statement.
I thought it only used top 100 percent when you put ORDER BY when creating views. Is there any performance advantage by putting TOP 100 PERCENT in creating view ?
Thanks
-
I don't think so. I recently noticed that top 100 percent is automatically added to the view definition when using the order by, but it is not automatically removed when the same order by is removed.
As for advantages, I can't think of any (without the order by).
-
No there is not. May have been the tool they were using if was a visual tool. EM will do this in the visual designer if you set and order by and then removew it the 100 percent will not go away. Most likely little to no code cleanup took place.
-
just to add ...
Remove the top 100 percent. Even if it doesn't hurt, it puts you on the wrong foot when you encounter it.
Even when an order by is within the view, chances are that someone just put it in to select the view and see the data sorted like specified in the order by clause. This is nolonger the case with SQL2005.
Reason for this ? The order by in the view, only serves the dataselection of the view itself. If you want to retreive data in an ordered way, specify an order by in your query.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It is a nice clue that the author may not be the brightest T-SQL bulb in the pack, however.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply