Discussion in 'The Lighter Side of Being a DBA' started by Madhivanan, Sep 28, 2007.
What do you think of this?
Not really suprising.
With SQL Server 2005 you cannot rely on the fact that the FROM clause is evaluated before the WHERE clause. Microsoft calls the "unsafe expressions". Here's the corresponding BOL passage:
"SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits:"
which is at: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm
If you ask me, that is a problematic behaviour as it violates the SQL standard. It clearly states that FROM comes before WHERE. Of course implementations are free to optimize where they can, however the final resultset SHOULD NOT BE AFFECTED. Clearly it is now affected as the statement errors out. All other RDBMS such as Oracle, Teradata, and DB2 don't do this sort of thing. There is a Connect issue raised by Steve Kass which was closed as "by design". So, yes it is a feature of SQL Server.
The best advise to give is, of course, to get your model right in the first place.
Thanks Frank 
Separate names with a comma.