I mentioned that I spotted some regression in SQL Server 2008 SP1 regarding the ORDER BY within a VIEW. The history behind this is that the ORDER BY clause was allowed in SQL Server 2000 Views and many applications were built using these views. Upgrade to SQL Server 2005 or SQL Server 2008 with no changes to the application and now the data comes out in a random order. After fixing this in SQL Server 2005 SP2 CU2 and in SQL Server 2008 CU1 unfortunately the bug is back in SQL Server 2008 SP1.
Here is the Knowledgebase number: 926292 FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order
There is a simple way to test this out. SQL Server 2005 and 2008 allow the ORDER BY in a View only if the TOP function is being used. Therefore TOP (100) PERCENT should do the trick. SSMS 2008 has a modify view feature that is now called “Design”. As an example, go under the sample database AdventureWorks2008 in Object Explorer and right click the View named HumanResources.vEmployee and choose Design. The Query Designer opens up with a diagrammatic depiction of the stored query object we call a View. In the Criteria Pane (under the diagram pane) scroll down to LastName and select a Sort Order of 1. This will add an ORDER BY LastName clause to the view query but it will also add a TOP (100) PERCENT clause to the view query. Close the Query Designer window and you will be prompted to save, so click Yes.
Now to test the view, right-click and choose Script View As, SELECT To, New Query Window. This generates a default SELECT statement for all columns and all rows for the View. Before executing, we need to turn on the Include Actual Execution Plan option in the toolbar or by the right-click menu. Hit Execute and 290 rows should be returned but in random order. Looking at the execution plan you will notice that there is no sort step in the plan. This is the bug.
To contrast this, if you go back and modify the View using the Design feature again and change TOP (100) PERCENT to TOP (99.999) PERCENT, save and test again using the same SELECT statement, you will see an explicit sort step in the plan and the data will be displayed in LastName order. This bug was fixed in CU1 but then reappeared in SP1. Maybe CU2 or CU3 was the culprit as another fix may have undone this one. SP1 really is just CU1, CU2 and CU3 combined.
With many customers looking to upgrade from SQL Server 2000 directly to SQL Server 2008, these backward compatibility issues become important. Regression makes you nervous as a product becomes mature and new bug fixes cause other bugs to reappear.
Yogi Berra said “This is like Deja Vu all over again”. I didn’t know he was a DBA?
Cheers
Brian
Recent Posts:
SP1 Uninstall works just fine – eventually…
