Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: A More Performant Alternative To COLUMNS_UPDATED()

Database Programming: A More Performant Alternative To COLUMNS_UPDATED()

  • Comments 2
  • Likes

(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 hereI 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?

 

Comments
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment