Computed Columns, Persisted Expressions, Indexability, and Determinism

Learning outcomes

ServiceHub repeatedly filters customer codes after trimming/uppercasing them and recalculates labor cost from minutes and hourly rate. The application team wants to materialize these expressions in every query; the DBA proposes computed columns. A computed column can make a derived rule reusable and, when eligible, indexable—but persistence and indexing add correctness prerequisites and write/storage costs.

1. Computed columns derive values from other columns

A computed column has an expression instead of being directly inserted or updated. By default it is virtual: SQL Server calculates it when needed. Marking it PERSISTED stores the computed value physically and maintains it as dependent columns change. Persistence can enable some index scenarios and avoids recomputing the value at read time, but it increases row storage and write work.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab04') IS NULL EXEC(N'CREATE SCHEMA lab04 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab04.WorkOrderBilling;CREATE TABLE lab04.WorkOrderBilling(    work_order_id bigint NOT NULL CONSTRAINT PK_WorkOrderBilling PRIMARY KEY,    customer_code varchar(24) NOT NULL,    labor_minutes int NOT NULL,    hourly_rate decimal(10,2) NOT NULL,    normalized_customer_code AS UPPER(LTRIM(RTRIM(customer_code))),    labor_amount AS CONVERT(decimal(12,2), labor_minutes * hourly_rate / 60.0) PERSISTED,    CONSTRAINT CK_WorkOrderBilling_Minutes CHECK (labor_minutes >= 0),    CONSTRAINT CK_WorkOrderBilling_Rate CHECK (hourly_rate >= 0));GOINSERT lab04.WorkOrderBilling(work_order_id, customer_code, labor_minutes, hourly_rate)VALUES (1001,' cust-001 ',90,80.00),       (1002,'CUST-002',45,100.00),       (1003,'cust-001',30,80.00);GOSELECT * FROM lab04.WorkOrderBilling ORDER BY work_order_id;GO

The normalized code is virtual, while labor amount is stored because it is PERSISTED. Neither column accepts direct INSERT/UPDATE values; their values flow from base-column changes.

2. Determinism and precision control what can be indexed

An indexed computed expression must meet SQL Server requirements around ownership, determinism, precision, result data type, and session SET options. Deterministic means the expression returns the same result for the same referenced inputs under the required database/session conditions. Precise excludes expressions whose result depends on imprecise types such as float/real in disallowed key scenarios.

USE ServiceHubLab;GOSELECT cc.name,       cc.definition,       cc.is_persisted,       TYPE_NAME(cc.user_type_id) AS data_type,       COLUMNPROPERTY(cc.object_id, cc.name, 'IsDeterministic') AS is_deterministic,       COLUMNPROPERTY(cc.object_id, cc.name, 'IsPrecise') AS is_preciseFROM sys.computed_columns AS ccWHERE cc.object_id = OBJECT_ID(N'lab04.WorkOrderBilling')ORDER BY cc.column_id;GO

Metadata is more reliable than visually judging an expression. Also remember that deterministic date-string conversion can depend on style/language/dateformat; production schemas should use unambiguous deterministic forms rather than locale-sensitive implicit conversions.

3. Deliberately wrong approach: persist a nondeterministic clock

A developer attempts to add AS GETDATE() PERSISTED as an “automatic last seen” column. That is conceptually wrong: current time is not a deterministic function of the row’s base columns. A persisted/indexed derived value must be reproducible from its dependencies.

USE ServiceHubLab;GOBEGIN TRY    EXEC(N'CREATE TABLE lab04.BadComputed    (        id int NOT NULL PRIMARY KEY,        captured_at AS GETDATE() PERSISTED    );');END TRYBEGIN CATCH    SELECT ERROR_NUMBER() AS error_number,           ERROR_MESSAGE() AS error_message;END CATCH;GODROP TABLE IF EXISTS lab04.BadComputed;GO

The precise error number/text can vary with engine details; the invariant is that a nondeterministic computed expression is not eligible to be persisted/indexed as though it were a stable function of row data. Store event timestamps explicitly (often with a DEFAULT) when they represent facts captured at write time.

4. Index a computed access path only after meeting SET-option requirements

SQL Server requires specific session options when creating and using indexes on computed columns: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must be ON; NUMERIC_ROUNDABORT must be OFF. Client defaults can differ, so deployment scripts should set and verify them deliberately.

USE ServiceHubLab;GOSET ANSI_NULLS ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET ARITHABORT ON;SET CONCAT_NULL_YIELDS_NULL ON;SET QUOTED_IDENTIFIER ON;SET NUMERIC_ROUNDABORT OFF;GOCREATE INDEX IX_WorkOrderBilling_NormalizedCustomerON lab04.WorkOrderBilling(normalized_customer_code)INCLUDE (labor_amount);GOSELECT i.name,       i.is_unique,       i.is_disabled,       i.has_filterFROM sys.indexes AS iWHERE i.object_id = OBJECT_ID(N'lab04.WorkOrderBilling');GO

The index makes the normalized expression an explicit access path. A query written against the same computed column can seek when the optimizer estimates that useful. Do not claim a seek from a three-row table as a universal performance result; inspect an actual/estimated plan with representative data in the later indexing/optimizer chapters.

5. Computed columns centralize semantics—but make writes depend on those semantics

Persisted values and indexes must be maintained whenever their base columns change. If a frequently updated table accumulates many derived/indexed columns, write amplification and storage can outweigh read benefits. Changing the expression is also a schema migration, not an application-only refactor. Computed-column definitions can depend on database collation, UDF ownership, data types, and SET options, creating deployment prerequisites that must be documented.

Schema binding and function dependencies: if an indexed computed expression calls a user-defined function, the function must satisfy the ownership/determinism rules and schema binding becomes part of the dependency contract. SCHEMABINDING prevents referenced objects from being changed underneath a bound function without SQL Server detecting the dependency. This is not a general recommendation to hide all logic in scalar functions; it is a reminder that indexed derived data must be reproducible from stable dependencies whose owners and definitions are governed across environments.

Statistics: an index created on a computed column has statistics that describe the distribution of that computed key, just as other index statistics do. Those statistics can help cardinality estimation, but they do not guarantee that a query written with a syntactically similar expression will use the index. Expression matching, parameter types, collation, SET options, selectivity, competing indexes, and the optimizer cost model still matter. If the computed definition changes, review dependent indexes and statistics as part of the migration instead of treating them as invisible implementation details.

USE ServiceHubLab;GOUPDATE lab04.WorkOrderBillingSET labor_minutes = 120,    customer_code = ' CUST-009 'WHERE work_order_id = 1001;GOSELECT work_order_id,       customer_code,       normalized_customer_code,       labor_minutes,       hourly_rate,       labor_amountFROM lab04.WorkOrderBillingWHERE work_order_id = 1001;GO

SQL Server updates both persisted computed storage and any dependent index entries as part of the row change. Correctness remains transactional; the cost is additional work that should be justified by actual query needs.

6. Hands-on lab: verify expression, metadata, and SET state

USE ServiceHubLab;GOSELECT    SESSIONPROPERTY('ANSI_NULLS') AS ANSI_NULLS,    SESSIONPROPERTY('ANSI_PADDING') AS ANSI_PADDING,    SESSIONPROPERTY('ANSI_WARNINGS') AS ANSI_WARNINGS,    SESSIONPROPERTY('ARITHABORT') AS ARITHABORT,    SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS CONCAT_NULL_YIELDS_NULL,    SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QUOTED_IDENTIFIER,    SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS NUMERIC_ROUNDABORT;SELECT cc.name,       cc.definition,       cc.is_persisted,       COLUMNPROPERTY(cc.object_id, cc.name, 'IsDeterministic') AS is_deterministic,       COLUMNPROPERTY(cc.object_id, cc.name, 'IsPrecise') AS is_preciseFROM sys.computed_columns AS ccWHERE cc.object_id = OBJECT_ID(N'lab04.WorkOrderBilling');SELECT work_order_id, normalized_customer_code, labor_amountFROM lab04.WorkOrderBillingWHERE normalized_customer_code = 'CUST-001';GO

Verification checklist

• You can distinguish virtual from PERSISTED computed storage.
• You inspected determinism/precision rather than guessing.
• You reproduced a nondeterministic persistence failure safely.
• You created an index only after setting the documented session options.
• You can explain why indexing a computed column adds write/storage maintenance rather than being a free read optimization.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab04.WorkOrderBilling;GO

7. Production judgment and next bridge

Computed columns are strongest when they encode stable, deterministic domain transformations that many queries need and when measured workloads justify persistence or indexing. Prefer explicit persisted business facts for nondeterministic events such as “captured at” timestamps. Treat changes to computed expressions and their indexes as schema migrations with dependency, SET-option, storage, and rollback review.

Lesson 5 applies another SQL Server schema feature to time: system-versioned temporal tables. Unlike a computed column, temporal versioning maintains old row versions in a history table so you can ask what data looked like at a past system time. That convenience has retention, indexing, schema-evolution, security, and audit-boundary consequences.

Authoritative references

• Indexes on computed columns — ownership, determinism, precision, data-type and SET-option requirements
• Specify computed columns — virtual versus PERSISTED behavior and limitations
• SQL Server 2025 build versions — current engine baseline