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:

CREATE TABLE Sales.OrderLines
(
    ...
    UnitPrice decimal(18, 2) NOT NULL,
    PickedQuantity decimal(18, 3) NOT NULL,
    LineTotal AS ROUND(UnitPrice * PickedQuantity, 2)
    ...
)

Each time the value from that LineTotal 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 far more often 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, if you can’t change the code that inserts the rows to include the LineTotal, is to define a regular column, then create a trigger on the table that sets the value whenever the row is inserted or updated.

You can do that in SQL Server, but we don’t need to.

PERSISTED computed columns

Instead, you can create a PERSISTED computed columns. You change the definition of the column like this:

CREATE TABLE Sales.OrderLines
(
    ...
    UnitPrice decimal(18, 2) NOT NULL,
    PickedQuantity decimal(18, 3) NOT NULL,
    LineTotal AS ROUND(UnitPrice * PickedQuantity, 2) PERSISTED
    ...
)

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, and reading this value is just like reading from any other column.

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

2026-03-07