January 24, 2008 at 2:07 am
Comments posted to this topic are about the item Sorted views in 2005 and TOP clause
February 21, 2008 at 11:40 pm
top 100 percent works fine by me. Tried it on SQL server 2005 MMS. plain select = 888 rows, top 100 percent = 888 rows.
February 22, 2008 at 12:50 am
The problem is not with the number of rows, but with the sort order. If You try to submit a query select top 100 percent .... from ... order by ... then it is likely that the result WILL NOT be sorted int the order You required. The trick "top 100 percent ....... order by ...." works in SQL Server 2000 but it does not work anymore in the SQL Server 2005 (at least not with more sophisticated queries). There are a few articles written on this subject. The essence of the problem is that in SQL Server 2005 query optimizer ignores "order by" when it encounters "top 100 percent" in the select clause.
So this trick is not only supposed to get all rows, but to get them all in the specified order.
February 22, 2008 at 1:42 am
Why to do this? ORDER BY should not be inside view and I think it is good that it is not supported by SQL 2005!
February 22, 2008 at 1:47 am
The actual problem is that a view is conceptuallly equal to a table - that is, it has no implied sort order. The only way to be sure to get the rows in the required order is to use an ORDER BY in the final SELECT.
The TOP 100 PERCENT / ORDER BY "trick" has never been documented or supported. It just "happened" to work that way - and when it "happened" to work another way, in SQL 2005, lots of badly coded apps experienced failures.
Your article promotes the use of a new "trick" that is not documented or supported but just "happens" to work that way on SQL 2005 - stop for a while and consider what willl happen after the move to SQL 2008. What if this trick stops working?
Frankly, I am apalled that this article was even published here on SQL Server Central. I voted "awful" and I hope that Steve will decide to pull it.
February 22, 2008 at 2:12 am
I second Hugo - the kludge to do ordering in a view is not something to advocate. The SQL optimiser is efficient because it doesn't guarantee an order unless explicitly requested by the outer query. The efficiency is gained because it may find that it's more efficient to follow a certain index which would otherwise be very inefficient if a particularly ordering was requested.
The optimiser correctly sees 100% and ignores the request. Smart 🙂
I believe I read in an article that compatibility level 80 (perhaps with a trace flag?) would restore the old behaviour.
Finally, I suppose the only benefit of such an article (and it certainly shows the determination to work around the issue) is that if you have a legacy app that relies on such behaviour you could rewrite the view(s) with the new TOP clause...
February 22, 2008 at 2:15 am
1.
There are a lot of artricles on the SQL Server Central website about undocumented features of the SQL Server and which were found by readers quite useful.
If there is a way to solve the big problem You currently have and there is no known documented solution for it than You will probably use undocumented one, even for the price of the problem in the future release of the product. Maybe it is not a good practice but when You are by the wall, You do not care.
If You do not approve it, You do not have to use it.
2.
You are right that any undocumented feature may change with the next version of the product but the same happens to some of documented features. They become "depreciated" and they will in all likelihood will not be available with the one of future releases (this will happen with extended stored procedure).
This is unavoidable , regardless of, that if You use documented or uncodumented feature (with that difference that undocumented ones may change with the new Service Pack).
February 22, 2008 at 2:24 am
3.
Moreover, if You had an application using such feature which was changed and You have an option to change a code of it and than prepare an update of binary files for customers and the second option to use a dirty trick and simply change the definition of the view, which one would You choose Yourself, considering implementation, testing and publishing time and costs ?
February 22, 2008 at 3:02 am
skra (2/22/2008)
1.There are a lot of artricles on the SQL Server Central website about undocumented features of the SQL Server and which were found by readers quite useful.
True. And I always vote "awful" if I see an article that pushes undocumented behaviour without warning the readers that the behaviour is undocumented, unsupported, and likely to change with the next version, the next service pack, or even when the weeather changes.
The fact that you and I have never seen the "new" trick for "ordered views" fail is no guarantee that it never will. Suppose a big company reads your article, implements this "trick" and then makes a million dollar loss because it happens not to work for them - will you take responsiblility?
If there is a way to solve the big problem You currently have and there is no known documented solution for it than You will probably use undocumented one, even for the price of the problem in the future release of the product. Maybe it is not a good practice but when You are by the wall, You do not care.
But in this case, there is a way to solve the problem. By realizing that the whole concept of an "ordered view" (I can't even write it without the quotation marks) is fundamentally wrong, and that the order of a query can only be imposed by the ORDER BY clause of the outermost query.
2.
You are right that any undocumented feature may change with the next version of the product but the same happens to some of documented features. They become "depreciated" and they will in all likelihood will not be available with the one of future releases (this will happen with extended stored procedure).
This is unavoidable , regardless of, that if You use documented or uncodumented feature (with that difference that undocumented ones may change with the new Service Pack).
When a documented feature changes, you get advance warning. The feature is marked as deprecated but still supported for one, two or even three versions. After that, the feature is in some cases still around for one or two more versions in a compatibility setting. And the change is widely documented, so that any self-respecting DBA will know about it, and can prepare to replace code relying on that feature somewhere during the several years that the feature will still be around as a deprecated feature.
If the next service pack changes behaviour of views using your "trick", there wil be no advance warning, no grace periode, no nothing. Just application errors, angry users, and a need to update your resume.
3.
Moreover, if You had an application using such feature which was changed and You have an option to change a code of it and than prepare an update of binary files for customers and the second option to use a dirty trick and simply change the definition of the view, which one would You choose Yourself, considering implementation, testing and publishing time and costs ?
It's unlikely that I would ever have such an application, except possibly by inheritance.
And in such a case, I'd definitely push to fix the real problem instead of replacing a kludge with another kludge. It might take a bit more time now, but it'll save lots of time in the long run.
If a manager would presure me to go for a quick fix, I'd ask him why there's never time to do it good but always time to do it over. And then I'd remind him that he can pick two out of "good, cheap, fast" - but if "good" isn't in, then neither am I.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply