whenriksen (6/18/2013)
One other thing:Please note that I have used TOP 100 PERCENT in the SELECT statement in this function because I want to retain the ORDER BY clause, which is otherwise not allowed in InLine table-values functions.
Top 100 Percent... Order by no longer has any effect on the result. SQL 2008 ignores that combination. You can read more here.
Absolutely true. They changed the optimizer to think that ORDER BY isn't needed when TOP 100% is used as an "optimization" so it doesn't work anymore. However, TOP 2147483647 (Largest number for an integer) still works.
Even with that, I agree that if you need the final result set to be sorted, you cannot rely on the sort of a sub-query to do it for you.
--Jeff Moden
Change is inevitable... Change for the better is not.