One of the restrictions of SQL Server 2005 (and now in 2008) is that you cannot include an ORDER BY within a view. This presumably is to remove the possibility of a “double sort” where the view defines a sorting order then the application requests a different sort order when the view is accessed. If you try to create a view with an ORDER BY it will now produce an error. This seems fine except that when you upgrade from SQL Server 2000 with an ORDER BY in a view already, it will allow the view to upgrade unchanged but the query optimizer will actually ignore the ORDER BY at runtime. This means that applications that presented data in a particular order suddenly see a different sort order after an upgrade to SQL Server 2005. Same tables, same indexes, same views, different sort order. Not good. So how do we deal with this lack of backward compatibility?
One exception to the new rule of “no ORDER BY in a view” is when the new TOP function is used in the view also. The TOP function allows you to limit the number of rows returned by the query by either a number or a percentage. So being clever, more than one person thought up a rather nice loop hole: why not specify TOP 100 PERCENT and then the ORDER BY will be allowed, won’t it? The plot thickens…
Yes, the error will not appear and the view will be created. In fact, someone at Microsoft spotted this very loophole and included it in SQL Server Management Studio. Try it yourself. Right-click on a view and select Modify (or Design in SQL Server 2008) then specify a sort order in the Criteria Pane and press Enter. Instead of indicating an error, SSMS will insert the ORDER BY in the view and will generate a TOP 100 PERCENT within the SELECT as well. Sneaky, eh? Well, not so fast. Don’t update those views just yet. When you save the view it will not complain but when you access the view, it will not be sorted. The ORDER BY will be ignored. Dang. So close and yet so far.
In my testing with SQL Server 2008, I requested to look at the Actual Execution Plan in SSMS and indeed, a sort was nowhere to be seen. But then I tried something radical. I tried TOP 99 PERCENT in the view with the ORDER BY. It worked. The execution plan showed an explicit sort in the plan and the data was sorted correctly. This is not a solution but it does provide evidence that Microsoft somehow tried to close the TOP 100 PERCENT loophole by just ignoring the sort. Not good.
So what is the solution? Well, we have to go into our application and wherever the upgraded views are accessed, we need to add an explicit ORDER BY to get it to sort correctly. This is extra work we don’t need, but to be honest, this is the way we should have done it in the first place. It is a best practice to explicitly sort the data when accessing the view. But Microsoft broke the golden rule of backward compatibility. If you let it work before, please let it work again in the new release. Otherwise, users will be once bitten, twice shy. And that means they won’t want to upgrade at all. And that would be a shame.
Later
Brian
Recent posts
