What comparison method is better?

Question

I have a trigger in a table with a good number of columns (perhaps around 100) and quite a lot of updates (for some definition of "a lot of"). If any of some fields have changed, the trigger inserts some data in another table.

For obvious reasons, I want this trigger to run as fast as possible. What's the best method to do the comparison? For now I have those:

IF NOT EXISTS (SELECT * FROM Inserted i, Deleted d WHERE 
    i.Fld1 = d.Fld1 AND i.Fld2 = d.Fld2 AND
    i.Fld3 = d.Fld3 AND i.Fld4 = d.Fld4 AND
    i.Fld5 = d.Fld5 AND i.Fld6 = d.Fld6 AND
    i.Fld7 = d.Fld7)     
    THEN ...

---

IF ((SELECT Fld1 FROM Inserted) <> (SELECT Fld1 FROM Deleted) OR
    (SELECT Fld2 FROM Inserted) <> (SELECT Fld2 FROM Deleted) OR
    (SELECT Fld3 FROM Inserted) <> (SELECT Fld3 FROM Deleted) OR
    (SELECT Fld4 FROM Inserted) <> (SELECT Fld4 FROM Deleted) OR
    (SELECT Fld5 FROM Inserted) <> (SELECT Fld5 FROM Deleted) OR
    (SELECT Fld6 FROM Inserted) <> (SELECT Fld6 FROM Deleted) OR
    (SELECT Fld7 FROM Inserted) <> (SELECT Fld7 FROM Deleted))
THEN...

I would usually prefer the first method, as it's more compact and seems more idiomatic. However, when speed is an issue, how should I do it?

Answer 1

The second version is completely broken for multi-row UPDATES, so for that reason alone, I'd do a variant of the first:

INSERT INTO ANotherTable (Column1, COlumn2, /* Etc */)
SELECT i.Column1,d.Column1, /* Other COlumns */
FROM
    inserted i
        inner join
    deleted d
        on
            i.Fld1 = d.Fld1 and /* For each column in PK */
            i.Fld2 <> d.Fld2 /* For each non-PK column */

Assuming the PK is stable and unchanging

Answer 2

Why don't you test your changing columns by using IF UPDATE(Column1,Column2,...) That will let you know whether any of the columns have changed that you're interested in. See http://msdn.microsoft.com/en-us/library/ms187326.aspx for details on the UPDATE() function.

You'll also be able to use it if the PK has been modified, whereas comparing between the inserted and deleted the way you're trying to go about it will miss out on changes to the PK.

The solution involving testing the inequality of all of the fields will fail unless you SET ANSI_NULLS OFF before comparing.

For example:

create table table1 ( a varchar(4), b varchar(4) null)
create table table2 ( a varchar(4), b varchar(4) null)
go

insert into table1 ( a, b ) select 'asdf', null
insert into table2 ( a, b ) select 'asdf', 'zzzz'

--Expect no results

select *
from table1
inner join table2
on a.a = b.a
where a.b <> b.b

set ansi_nulls off

--Expect 1 result

select *
from table1
inner join table2
on a.a = b.a
where a.b <> b.b

Of course, you could put in additional tests rather than going with the ansi_nulls option, but that gets particularly crazy if you're doing this with many fields. Also: you must set ansi_nulls off prior to creating the trigger - you can't turn it on and off inside the trigger, so the whole thing must have the same ansi_nulls setting.