Re: Order of execution in logical expressions

Wayne Erfling (wayne_erfling@xxxxxxxxxxxxxx) writes:
GlacierI'm a little confused about order of execution in logical
expressions.

In a statement like this:

SELECT * FROM myTable
WHERE PK_ID > 0 OR
(PK_ID <=0 AND 1/0 > 5)

apparently statement compilation jumps in and detects the divide by zero
error, even though that clause would never be executed, because all
PK_IDs in myTable are in fact > 0.

T-SQL is not like C and Perl that shortcut in a well-defined manner,
but is like Pascal and the optimizer is free to whichever it likes.

There is only one way to control evaluation order, and that is the CASE
expression:

SELECT *
FROM myTable
WHERE 1 = CASE WHEN PK_ID > 0 THEN 1
WHEN PK_ID <=0 AND 1/0 > 5 THEN 1
ELSE 0
END

But it's nice to know that if you have:

SELECT *
FROM myTable
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE ...)
AND @myparam = 1

SQL Server will most likely not even touch the table in the subquery if
@myparam is <> 1.


This question arose because of a sub-query in an UPDATE TRIGGER:

...

IF NOT UPDATE(fieldname) OR
(SELECT DISTINCT fieldname FROM INSERTED) IS NULL)


For a multiple-row update, at execution time this statement returned an
error about multiple rows being returned from a sub-query.

But this has nothing to do with execution order. The SELECT DISTINCT
query can never be right. Replace with

NOT EXISTS (SELECT * FROM inserted WHERE fieldname IS NOT NULL)

When UPDATE(fieldname) is TRUE there can only be one value for fieldname,
the value supplied in the UPDATE statement.

There can be many values. Keep in mind a trigger fires once per statement,
not once per row.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: query performance help needed
    ... how that affects the execution time. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... rebuild indexes and if we go on production maybe it will be better... ...
    (comp.databases.ms-sqlserver)
  • Re: Order of execution in logical expressions
    ... > GlacierI'm a little confused about order of execution in logical ... > expressions. ... NOT EXISTS (SELECT * FROM inserted WHERE fieldname IS NOT NULL) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: CASE problem
    ... Syntax error converting the varchar value 'ST-000001' to a column of data type int. ... If the different THEN expressions are of different data types, they will be converted according to the data-type precedence rules in SQL Server. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: TRANSACTIONS in a WHILE LOOP. Flow Question
    ... I obviously do not understand error handling as I should. ... loop below where does the point of execution move to after an error? ... Error handling in SQL Server 2000 is a fairly messy story, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: query performance help needed
    ... why I guessed that the execution plan is gone. ... executes this sp from 30 databases. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)