Views and ORDER BY

re: Views and ORDER BY

arse — Thu, 07 Mar 2013 06:45:56 GMT

try SELECT TOP 100 PERCENT instead

re: Views and ORDER BY

Dave Ballantyne — Thu, 07 Mar 2013 11:31:10 GMT

Top 100 percent gets optimized out and has no effect.

BUT.....

Ordering in a view is contradictory to RDBMS practice.

The only place you should order is from a result set.

A view is not a result set.

You can select from a view to generate that result set and order that, but that is not the same as the view having order.

Infact, i would go so far to say that even if you use TOP(X) order by , the only guarentee is that those rows returned are the TOP(X) and you cannot rely upon the order of those.

re: Views and ORDER BY

David Markle — Sat, 09 Mar 2013 15:26:20 GMT

We had this issue with SQL 2005 as well, up until the first or second service pack. (I'd like to point out that the "we" I refer to is the community, and not the software I've written :D )

MS rightfully optimized out the TOP 100 PERCENT from views and tons of software which relied on this failed to work. The root cause of all of this nonsense was that the drag n' drop view designer in SSMS actually GENERATED these view queries. This pattern spread like a virus until it found its way into tons of software which promptly broke when the optimizer stopped ordering by views.

What do they need to do? IMO the best solution is to completely disallow SELECT TOP 100 PERCENT ... ORDER BY in view definitions altogether and throw an error when the view is defined. Such a view definition could and should still compile in compatibility mode, but it would force software which relies on this pattern to break when views are moved over to a new compatibility mode.

Of course, this wouldn't necessarily completely eliminate this problem, as you could always SELECT TOP <something bigger than the table will ever be> in a view to try to replicate the behavior, but those patterns are far, far less common than the SELECT TOP 100 PERCENT ... pattern.

re: Views and ORDER BY

Sun, 10 Mar 2013 10:40:08 GMT