In a previous blog entry, I discussed the issue surrounding ORDER BY’s in VIEW’s and backward compatibility. The blog was entitled: “ORDER BY in a View is not allowed – or is it?”. Well, it looks like this one is fixed in SQL Server 2008 Cumulative Update 1….
According to the CU1 web page, the list of fixes includes this:
“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” – KB 926292 https://support.microsoft.com/kb/926292/ I outlined how the loophole of including a TOP 100 PERCENT function allowed the ORDER BY to be included in a View in SQL Server 2008, otherwise it would produce a syntax error. However, the query optimizer would ignore the sort when using this loophole. I verified this by specifying TOP 99 PERCENT then observing the execution plan and finding an explicit SORT step which was missing with the TOP 100 PERCENT version. Well, now it works, which means that Views that had ORDER BY’s in SQL Server 2000 and are now upgraded, will work as they did previously. Which means our applications will work as they did before.
Now that’s backward compatibility. Thank you Microsoft!
Cheers
Brian
