ORDER BY in Views: A SQL Server 2005 Gotcha!

Hugo Kornelis just brought up a very interesting point about SQL Server 2005, and its behavior when using views with ORDER BY clauses in them.

You may be aware that SQL Server 2000 (and previous versions) allowed you to essentially create an ordered view -- something that's not really supposed to be allowed -- simply by using TOP 100 PERCENT:

CREATE VIEW myView AS
SELECT TOP 100 PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

In fact, this is exactly what Enterprise Manager, and as Hugo points out, Management Studio , does when you define a sorted view with the query designer (truly a tool of the Devil himself).

When I do a
SELECT * FROM dbo.myView;
in SQL Server 2005 I get the following results:

Wyman
Jones
Watts
Richards
Jagger

As you can imagine, this will break any query you have in your databases which rely on this "feature". The truly amazing thing is that SQL Server Management Studio still allows users to choose a sort order in views without so much as a warning dialog box!

So I wonder... Would this be considered a bug in the query parser, if it allows syntax that can't possibly work? Is it a bug in the engine? Should this evil trick still work? All of this got me to thinking (uh oh) -- how could one work around this issue if one is upgrading a legacy database?

And, what if you were to create the view like this?

CREATE VIEW myView AS
SELECT TOP (99.9999999999999) PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

Lo and behold, it "works" -- when you query the view, the results are shown in order. But add just one more nine:

CREATE VIEW myView AS
SELECT TOP (99.99999999999999) PERCENT LastName
FROM dbo.Stones
ORDER BY LastName;

and you're back to the unordered list. Strange. I suppose that the SQL Server engine is doing a bit of rounding internally and throwing away the sort order altogether as an optimization so it won't have to perform a final sort on the resultset for no reason. I really have no way to know exactly, though.

Think setting the compatibility mode to 80 will save you? Think again. This behavior is independent of the compatibility mode you use.

The moral of the story:
Before migrating your databases to SQL Server 2005, make sure you have no TOP 100 PERCENT ... ORDER BYs in your views.

Analyze every query you run which consumes views with TOP 100 PERCENT ... ORDER BYs, and make sure they're ready for the new behavior.

(This behavior is consistent both in SQL 2005 SP1 as well as the SQL 2005 SP2 Community Technology Preview)


(Photo by FRANKIEB)