SQL: Computed Columns: It’s a matter of persistence

Most SQL Server developers are aware that they can create computed columns. We do that by defining a column AS some expression like this:

Each time the value from that column is queried, the calculation is performed so the result can be returned. This makes sense when the value is changing regularly and the value is queried infrequently.

However, according to my completely subjective statistics, most computed columns are queried much more than they are ever changed. So why work the value out each and every time?

In some database products, the way to get around that is to define a regular columns, then create a trigger on the table that sets the value whenever the row is inserted or updated.

In SQL Server, we don’t need to do that. Back in SQL Server 2005, we got the option to created PERSISTED computed columns. You change the definition of the column like this:

When a computed column is defined as PERSISTED, SQL Server calculates and saves the value for you whenever the row is inserted or updated.

This means that you pay the calculation performance penalty only when the data changes, and your SELECT operations get to run at full speed, just like any other column.

Persistence is a good thing. It allows the value to be calculated now, not later when you need to query it.

2017-10-30