First, please take a minute to check out the first video from my old friend Chris Saxon:

This reminded me of a trick I’ve used in a couple of SQL Server databases using a similar technique.

When storing details of people, you often find that the first and last names have their own columns; this makes sorting easier, but if you’re trying to retrieve the whole name as a single column, then it’s a pain to always remember to write FirstName + ' ' + LastName. Initially, I made my life easier by adding this:

CREATE TABLE Person (
Id INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(100) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
FullName AS FirstName + ' ' + LastName PERSISTED
)

Better – we can now query on our computed FullName column and, because we’ve used the PERSISTED flag, there’s little to no overhead in doing so (the value is stored physically rather than being computed on the fly). However, in a moment of whimsy, I took it a step further:

ALTER TABLE Person ADD NameFormat TINYINT NOT NULL DEFAULT (0)

ALTER TABLE Person ALTER COLUMN FullName AS
  CASE (NameFormat)
    WHEN 1 THEN (LastName + N', ' + FirstName)
    WHEN 2 THEN (SUBSTRING(FirstName, 1, 1) + N'. ' + LastName)
    WHEN 3 THEN (LastName + N', ' + SUBSTRING(FirstName, 1, 1) + N'.')
    ELSE (FirstName + N' ' + LastName)
  END PERSISTED

Depending on the value of the NameFormat field, my name renders as either “Keith Williams”, “Williams, Keith”, “K. Williams”, or “Williams, K.”.

If name formatting was a big deal, then I’d recommend you do it in the application layer rather than SQL (and especially if you’re handling non-European names), but the approach above gives you a convenient way of storing basic formatted names without repeating code or calling scalar functions.