Persisted vs. Non-Persisted Computed Columns in SQL Server

What a Computed Column Actually Is

A computed column doesn't store a value you assign; it stores (or re-derives) the result of an expression over other columns in the same row. By default, it stores nothing at all.

CREATE TABLE dbo.Customers (
  CustomerID   INT           NOT NULL PRIMARY KEY,
  FirstName    NVARCHAR(50)  NOT NULL,
  LastName     NVARCHAR(50)  NOT NULL,
  FullName     AS (FirstName + N' ' + LastName)
);

-- FullName is NOT stored anywhere on disk.
-- Every SELECT that touches it re-runs the concatenation.
SELECT CustomerID, FullName FROM dbo.Customers WHERE CustomerID = 42;

That's a non-persisted computed column, the default. Add PERSISTED and SQL Server physically stores the computed value in the row, right alongside the real columns, and recalculates it only when a source column changes:

CREATE TABLE dbo.Customers (
  CustomerID   INT           NOT NULL PRIMARY KEY,
  FirstName    NVARCHAR(50)  NOT NULL,
  LastName     NVARCHAR(50)  NOT NULL,
  FullName     AS (FirstName + N' ' + LastName) PERSISTED
);

Same query, same result. Completely different cost profile, and it moves in opposite directions depending on whether you're reading or writing.

Persisted vs. Non-Persisted: What Actually Changes

When to Use PERSISTED

Persist the value when the cost of computing it is higher than the cost of storing it, or when SQL Server structurally requires storage to do something else with the column:

• The expression is expensive: string parsing, .value() against an XML column, multiple nested function calls, CLR scalar functions. Paying that cost once per write beats paying it on every one of a million reads.
• The column needs an index. An index on a computed column implicitly persists it (the index itself stores the value); explicit PERSISTED makes that visible and lets other consumers of the column benefit even without the index.
• The column has to be a PRIMARY KEY, part of a UNIQUE constraint, or the target of a FOREIGN KEY. None of that is possible without PERSISTED.
• Reads massively outnumber writes. Reporting tables, dimension tables, anything closer to read-mostly than OLTP.

When NOT to Use PERSISTED

• The expression is cheap. Integer arithmetic, a short concatenation, a single CASE: recomputing it at read time costs essentially nothing. Persisting it just spends storage and write-time CPU for no measurable read-time gain.
• The table is write-heavy. Every INSERT and every UPDATE that touches a source column now also recomputes and logs the derived value. On a table taking thousands of writes per second, that's a real, permanent tax.
• The column is rarely queried. No point paying on every write for a value almost nothing reads.
• The table is already large and I/O-constrained. Persisting widens every row, which means fewer rows per page, more pages per scan, and a bigger backup, for a column that may only be used occasionally.

What This Means for INSERT and UPDATE

With a non-persisted computed column, the write path is completely unaware it exists. INSERT and UPDATE statements against the base columns run exactly as fast as they would without the computed column, because nothing is evaluated until a later SELECT asks for it.

With PERSISTED, every write that changes a source column becomes slightly heavier:

-- FullName is PERSISTED, derived from FirstName + LastName
UPDATE dbo.Customers
SET LastName = N'Jankowski'
WHERE CustomerID = 42;

-- What actually happens under PERSISTED:
-- 1. LastName is updated as normal
-- 2. FullName is re-evaluated
-- 3. The new FullName value is written into the row
-- 4. Both changes are logged
-- 5. If FullName is indexed, the index is updated too

-- Under non-persisted, steps 2–5 simply don't happen.
-- FullName is recomputed the next time someone SELECTs it.

None of this is dramatic for a single-row UPDATE. It adds up when the same pattern runs at OLTP volume, or inside a bulk load: a persisted computed column with an expensive expression, sitting on a table that ingests millions of rows a day, turns "just a derived column" into a measurable slice of your total write cost.

Very Large Tables: Where This Gets Expensive

The write-time cost above is per-row and ongoing. There's a separate, one-time cost that's easy to underestimate: adding a persisted computed column to a table that already has data.

-- Table already has 400 million rows
ALTER TABLE dbo.Orders
ADD OrderTotal AS (Quantity * UnitPrice - Discount) PERSISTED;

-- ✗ SQL Server must evaluate the expression for every EXISTING row
-- ✗ Every row is physically rewritten to include the new value
-- ✗ This happens inside ONE transaction, fully logged
-- ✗ SCH-M lock on the table for the entire duration: no reads, no writes
-- ✗ Same profile as ALTER COLUMN on a huge table: log growth,
--   long blocking, and a rollback that's as expensive as the operation itself

This is the same size-of-data problem covered in ALTER COLUMN on Large Tables: SQL Server doesn't distinguish "add a persisted computed column" from "rewrite every row." The same batching and log-management strategies from that article apply here, because under the hood it's the same kind of operation.

Even once it's in place, a persisted column keeps costing you on a huge table in ways that don't show up in any single query plan: every row is wider, so fewer rows fit per 8 KB page, so full scans and range scans touch more pages, the buffer pool holds less useful data per gigabyte of RAM, and backups take longer simply because there's more to write out. On a few thousand rows none of this registers. On a table in the hundreds of millions, a persisted column that turned out to be unnecessary is a permanent, silent overhead.

The Limits of Computed Columns

Whether an expression can be persisted, and separately, whether it can be indexed, comes down to two properties SQL Server evaluates for every computed column: deterministic (same inputs always produce the same output) and precise (no floating-point rounding involved).

A non-deterministic expression is stuck as non-persisted forever, no matter how expensive it is to evaluate; you can't index around it either. A few other limits round out the list:

• No manual writes. A computed column is never a valid target for INSERT or UPDATE, persisted or not.
• No cross-table logic. The expression can only reference columns in the same row. Anything that needs another table means a view, a trigger, or an actual materialized/ETL step, not a computed column.
• Scalar UDFs need SCHEMABINDING. If the expression calls a user-defined function, that function must be schema-bound, and itself deterministic, before its result can be persisted or indexed.
• You can't ALTER the expression. To change the formula, you drop the column and re-add it with the new definition. If it was persisted, that's the same full-table rewrite as adding it fresh, not an incremental change.
• Row-size limits still apply. A persisted column counts toward the 8,060-byte row-size limit like any other fixed column; large results can push a wide table into row-overflow storage.

Checking What You Already Have

Before assuming a computed column somewhere is "probably persisted," check. It's a common surprise on inherited schemas:

SELECT
  t.name              AS TableName,
  c.name              AS ColumnName,
  cc.definition,
  cc.is_persisted,
  cc.is_deterministic
FROM sys.computed_columns cc
JOIN sys.tables t ON t.object_id = cc.object_id
JOIN sys.columns c ON c.object_id = cc.object_id AND c.column_id = cc.column_id
ORDER BY t.name, c.name;

Best Practices Checklist

• ☐ Default to non-persisted; only add PERSISTED when there's a concrete reason (indexing, key constraint, expensive expression, read-heavy access)
• ☐ Never persist a cheap expression just out of habit: recomputing simple arithmetic at read time is free, storing it isn't
• ☐ On write-heavy OLTP tables, measure the write-side cost of any persisted column before shipping it, not after
• ☐ Before adding PERSISTED to a column on a large existing table, treat it exactly like an ALTER COLUMN: test the rewrite, size the log, and consider a maintenance window
• ☐ Check determinism and precision requirements before designing the expression, not after discovering it can't be indexed
• ☐ Query sys.computed_columns when inheriting a schema instead of assuming the persistence state
• ☐ If you only need the value indexed, adding the index is often enough; you don't always need to also mark the column PERSISTED in the definition

The Bottom Line

A computed column's real cost doesn't disappear based on whether you write PERSISTED; it just moves. Non-persisted defers the cost to every future read. Persisted pays it once per write and stores the result, at the price of a wider row and, on an existing large table, a full rewrite to get there. Choose based on your actual read-to-write ratio and the cost of the expression itself, not on which option feels more "correct." On a lookup table with a few thousand rows, either choice is invisible. On a table with hundreds of millions, it's a decision that shows up in your storage bill and your backup window for as long as the column exists.