SQL Server Performance

Strange behaviours ion sql server 2005

Discussion in 'The Lighter Side of Being a DBA' started by Madhivanan, Sep 28, 2007.

  1. Madhivanan Moderator

  2. FrankKalis Moderator

    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.
  3. Madhivanan Moderator

    Thanks Frank [:)]

Share This Page