Database Programming: Feedback Requested -- Which Syntax Is More Maintainable?

  • Article

This might be something of an atypical post for this blog in that it's a solicitation of feedback rather than a "sermonette," but so be it. Perhaps this is the start of a positive trend.. :-)

I was looking over this code from a previous post:

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

..and I was struck by the insight that there had to be a better, more compact way to code this block. After a little tweaking, I found what I was looking for:

UPDATE

s
SET SupplementTitle = ISNULL(NULLIF(i.SupplementTitle,ISNULL(NULLIF (i.SupplementTitle,d.SupplementTitle),s.SupplementTitle)),dbo.fnStripLowAscii(s.SupplementTitle)),
FileAttachmentPath = ISNULL(NULLIF(i.FileAttachmentPath,ISNULL(NULLIF (i.FileAttachmentPath,d.FileAttachmentPath),s.FileAttachmentPath)),dbo.fnStripLowAscii(s.FileAttachmentPath)),
MachineDataReference = ISNULL(NULLIF(i.MachineDataReference,ISNULL(NULLIF (i.MachineDataReference,d.MachineDataReference),s.MachineDataReference)),dbo.fnStripLowAscii(s.MachineDataReference))
FROM inserted i
JOIN dbo.Supplement s
ON i.SupplementId = s.SupplementId
LEFT OUTER JOIN
deleted d
ON i.SupplementId = d.SupplementId

I've convinced myself that the two syntaxes are functionally equivalent, both in terms of results and performance.

I've also convinced myself that this is a scenario where brevity is not a virtue. I find the previous syntax to be far more maintainable.

If you were handed this function "cold" to support, which syntax would you prefer? Are there other scenarios where verbosity is a virtue?

Your feedback would be of great interest..

   -wp