Database Programming: A More Performant Alternative To COLUMNS_UPDATED()

  • Article

(updated 18 February 2006 to clarify version information)

A recent discussion with several colleagues reminded me of a hard-won insight I've been meaning to share here. This involves the code that we originally discussed here. I know the following to be true for SQL Server 2000. I've not tested the issue under SQL Server 2005, but I'd be surprised if this behavior is any different.

The issue centers around the COLUMNS_UPDATED() function, a trigger-oriented function which, as the name suggests, provides a list of columns impacted by the UPDATE statement which fired the trigger. The name suggests that the column will only be named if the value is updated; in fact, the column is named if it's named in the UPDATE statement.

This state of affairs can have profound implications -- COLUMNS_UPDATED() isn't much help if you've got expensive code to run when the value of a column changes. This is the exact scenario which led me to discover the following:

UPDATE s
SET SupplementTitle = CASE
NULLIF(i.SupplementTitle, d.SupplementTitle)
WHEN NULL THEN s.SupplementTitle
ELSE dbo.fnStripLowAscii(s.SupplementTitle)
END,
FileAttachmentPath = CASE
NULLIF(i.FileAttachmentPath, d.FileAttachmentPath)
WHEN NULL THEN s.FileAttachmentPath
ELSE dbo.fnStripLowAscii(s.FileAttachmentPath)
END,
MachineDataReference = CASE
NULLIF(i.MachineDataReference, d.MachineDataReference)
WHEN NULL THEN s.MachineDataReference
ELSE dbo.fnStripLowAscii(s.MachineDataReference)
END
FROM inserted i
JOIN dbo.Supplement s
ON i.SupplementId = s.SupplementId
LEFT OUTER JOIN
        deleted d
ON i.SupplementId = d.SupplementId

The value in the deleted table will already have been processed by the expensive function. If the values in inserted and deleted match, then the data hasn't changed and there's no need to alter the value in the base table. If they don't match, then the data has changed, so we need to run our expensive code against the new value.

In light of this insight, I've not been able to find a scenario where a COLUMNS_UPDATED() call is very useful. Has anyone reading this encountered a scenario where a use of COLUMNS_UPDATED() is both useful and optimally performant?