Re: Order of execution in logical expressions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for your REPLY.

I was thinking that because the IF UPDATE(columnname) function returns TRUE for the presence of columnname in the SET clause, that only a single value could result, even for multiple-row updates. But of course I could have something like SET columnname = othercolumn*2, and get multiple values for columnname.

In the actual application I'm working with, columnname had only a single value, so SELECT DISTINCT, if actually executed and not trapped as an error, would have returned only one value. I don't think I would go as far as saying "can never be right". (The SQL error message didn't say "the optimizer noted that multiple rows might be returned", it said that multiple rows were returned, which caused some confusion).

Too bad I can't access some property of the new value to tell me whether a single value was provided, without resorting to yet another nested query. While "TOP 1" or "MIN()" or "MAX()" will work-around the error message for our application as we actually use it (SET columname=scalarvalue), if we ever do use SET columname=expression it will make the wrong decision more or less arbitrarily.

Since we're talking about order of execution and the CASE statement, I should mentiont that I recently discovered that IF UPDATE() doesn't work properly in a CASE statement; apparently there are a few people who know about this already; something to do with the internal parsing generating an integer result which can then cause the CASE statement to generate a type error.

---Wayne


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9945F20412BD3Yazorman@xxxxxxxxxxxx
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: Order of execution in logical expressions
    ... expressions. ... But this has nothing to do with execution order. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Slow UPDATE and DELETE on SQL Server 2000
    ... I've increased the size of the TempDb database and noticed ... When I ran the update query I got an error message about not being ... I did read the execution plans incorrectly. ... > SQL Server process, memory, as well as the SQL Server specific counters. ...
    (microsoft.public.sqlserver.server)
  • Re: DTS ActiveX Scripting
    ... SQL Server Agent Account if the owner of the job is a SQL Server sysadmin ... As you can see the first> ActiveX script runs ok. ... DTSRun: Loading... ... ActiveX Scripting> encountered a Run Time Error during the execution of the> script. ...
    (microsoft.public.sqlserver.dts)
  • Re: How to capture SQL statement messages in DTS log file
    ... I have several SQL Server 2000s and a couple of SQL Server 2005 servers ... Then instead of PRINT in your DTS package, ... The execution of the following DTS Package succeeded: ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.dts)
  • Re: error in sql dts job .. help :(
    ... get matched with that of the sql server manager service user domain account. ... the execution of the script. ... Transformation Services Package Help file: ... Error source: Microsoft Data Transformation Services ...
    (microsoft.public.sqlserver.dts)