Retrofitting Schemas with Computed Columns

Computed columns allow us to bake logic into our schema. I find particular use for these when reviewing or optimising established solutions where you spot the quirky ways the data is being used.

Here I want to demonstrate 3 symptoms and show how different implementations of computed columns solve them.

Baking the expression

Common expressions in query patterns can be replaced in computed columns, for example the pattern:

SELECT FullName = CONCAT(FirstName, ' ', Surname), ...

If this is repeated in multiple areas of the solution, this could be implemented via a computed column. This also centralises the logic so changes such as adding a title or middle initial can take effect more easily across the solution.

This would be solved via a regular computed column:

ALTER TABLE dbo.Clients
ADD FullName AS CONCAT(FirstName, ' ', Surname);

The expression will be evaluated only when it’s needed and won’t require additional storage.

Persisting expensive expressions

In other situations, we have expressions which are expensive. Let’s say we’ve optimised an ETL process by using a record hash to identify changes:

SELECT RecordID,
    RecordHash =  HASHBYTES('SHA2_256',
        CONCAT_WS('|', FirstName, Surname, EmailAddress, PostCode)),
    ...

This might be acceptable for a few records, but from experience I can attest that if you’re handling a large volume of data, performance will be dragged down.

The solution is to persist the value so that it’s stored against the row and available like any other column:

ALTER TABLE dbo.Clients
ADD RecordHash AS HASHBYTES('SHA2_256',
        CONCAT_WS('|', FirstName, Surname, EmailAddress, PostCode))
    PERSISTED;

Persisted columns are stored in the table like a regular column is, and they’re refreshed when fields they rely on in the row change. This means the result is always pre-calculated so read performance is improved, however they’ll be recalculated when the row changes, so you’ll trade off overhead on the writes.

The minor caveat of persisting a computed column is that the expression must be deterministic. This essentially means for the same values, the result will always be the same. For example it can’t contain GETDATE() as the result changes.

Another feature of persisted columns is existing queries may not need to reference the computed column directly. If the optimiser sees an expression which mirrors the computed column definition, it may use the persisted value rather than recalculating it. Note that this behaviour isn’t guaranteed, it’s best to change references – but I’ve had positive experiences with it in the past.

As a side note, if you’re doing this type of hashing as part of batch ingestion, you may want to consider source-side filtering which may be of benefit too.

Searching expressions

The final type of expression could be either be simple or complex like both examples above, but the difference is when you want to search based on them. Let’s consider an all-too-common simple pattern:

SELECT ...
FROM dbo.Sales
WHERE DATEPART(YEAR, SoldDateTime) = @Year;

This shackles the optimiser as the DATEPART() function nullifies the ability to seek into an indexed SoldDateTime field. It’s a very simple expression like we saw in the first example, but in a data warehouse this will be very expensive at scale.

The solution for this is to create a computed column and then index it. It’s a mash-up of the solutions above: an expression we want to reuse, and persisting the data – this time optimised for searching:

ALTER TABLE dbo.Sales
ADD SoldYear AS DATEPART(YEAR, SoldDateTime);

CREATE NONCLUSTERED INDEX IX_SoldYear
ON dbo.Sales (SoldYear);

This time when the expression is evaluated it’ll persist only inside the index rather than at the row level in the table. The advantage of being in the index is that it’s ordered and therefore seekable to improve search performance.

Indexed computed columns have the same limitation as persisted columns that they must be deterministic. However they also have the benefit that the optimiser can spot them and use the indexed value instead of needing to recalculate.

Wrap up

Computed columns are a core feature in SQL Server, and wielding them effectively helps right from initial development, through to solving performance bottlenecks.

Here we’ve looked at the 3 options available:

• Basic computed columns: to consolidate repeated expressions
• Persisted computed columns: to save on expensive expressions
• Indexed computed columns: for effective searching of expressions

Persisted and indexed computed columns trade off the overhead of writes for performance improvements on reads. This is often a solid trade-off in read-heavy solutions, but the balance should be considered when implementing.

My personal favourite is the indexed computed columns. They’re such a versatile combination of the other two solutions, and I’ve experienced these completely removing performance bottlenecks with a single addition.

Next time you see an expression in a query and want to re-write it, consider if any of the implementations above may be a better fit, particularly if you see the same pattern repeated across multiple queries.