What is a computed column, and when should it be persisted?
Quick Answer
A computed column's value comes from an expression over other columns in the row (FullName AS FirstName + ' ' + LastName). By default it's virtual (computed on read). Add PERSISTED to store it physically — required to index it when the expression isn't precise/deterministic, and worth it when the expression is expensive and read often.
A computed column is defined by a formula over the other columns — full name from first and last, or a tax amount from price and rate. Normally it's calculated when you read the row and takes no storage. If you mark it PERSISTED, SQL Server stores it and keeps it updated on write. You do that when you want to index it — for example a persisted computed column holding JSON_VALUE(Data, '$.status') or an upper-cased search key, so a query filtering on that derived value can seek an index instead of scanning and computing.
- Persist + index a computed column to make a derived predicate sargable (e.g. index
YEAR(OrderDate)via a persisted column, or a normalized/upper-cased search key). - The expression must be deterministic to be persisted; must also be precise to be indexed without persisting.
- Great for exposing a JSON value (
JSON_VALUE(Data,'$.status')) as an indexable column.