Monday, January 1, 2007

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)









7 comments:

Anonymous said...

Hi Dave,

Thanks for lifting this from an unnoticed remark in a comment to a blog entry of its own. Allow me to comment some more.

First, the behaviour in SQL Server 2000 has never been documented or supported either. It just happens to work most of the time - but there's no guarantee that it will always work, even on SQL Server 2000.

Second, the modified 99.99999 percent trick on SQL Server 2005 is not documented or supported either. Now it may work (if you don't have too many nines), tomorrow it may fail. Bottom line: don't try to create oredered views, they can't exist -- by definition!!

Lastly, the behaviour of SSMS is obviously a bug. (I never liked how EM handled it, but then it at least worked most of the time). I was surprised not to find any reports about this yet at Microsoft's feedback site, so I have just submitted one. Feel free to vote for it if you suppport my bug report! Here's the URL: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249248
(Warning - long URL, might wrap!)

Anonymous said...

Hey Dave! That's what can happen when you use an undocumented feature. You just run that type of risk! (Only slightly not kidding)

Seriously, having worked with you, I'm pretty excited to see you sharing your knowledge and experience with others. Good for you man!

Hang in there and keep the posts coming!

Matt B. the DBA

IDisposable said...

Microsoft released a hotfix that deals with this today (6/13/2007) here

Anonymous said...

Just wanted to say thanks for the hint :)

My prof gave us an assignment that involved creating an ordered view... not quite sure what I would have done without this post!

-Christine

dmarkle said...

Christine:

Any time. But if your professor asked you to create an ordered view, it might be time for a new professor!


-D

Anonymous said...

nice post

Anonymous said...

Great post! easy to download