Posted by Erland Sommarskog on 01/09/07 09:08

Plamen Ratchev (Plamen@SQLStudio.com) writes:
> I believe you are referring to the behavior explained in this KB article:
> http://support.microsoft.com/default.aspx/kb/287515
>
> The way this query is written it follows exactly the workaround solution
> explained in the article (to apply any function or expression to the
> SELECT list columns rather than in the ORDER BY clause). According to
> the article then the query will achieve the expected results.

But observe the first paragraph under CAUSE. The article then bends over
backwards to do it anyway, which contradicts the first paragraph. I prefer
to trust that first paragraph that says the correct behvaiour is undefined.

> This query was tested with both SQL Server 2000 and SQL Server 2005.

Yes, but did you get expected results, because you should get them, or
was it mere chance? Compare the TOP 100 PERCENT .. ORDER BY in views that
many incorrectly relied on in SQL 2000, and then were bitten by in
SQL 2005.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]