Documenting Computed Columns: Definitions, Persistence and Determinism

Documenting computed columns means writing down the formula, not just the data type. A column that says decimal(21,2) tells you nothing about how it gets its value. Capture the expression, whether it is stored, whether it is precise and whether an index uses it.

The column nobody inserts into

A new teammate asks why their INSERT fails when it includes LineTotal. Another one copies a table to a test server and finds that LineTotal is now a plain column full of old values. Both problems start the same way. Nobody wrote down that LineTotal is a formula.

The demo builds one order table with two computed columns. LineTotal is Qty times UnitPrice and is persisted, so SQL Server stores the result. Ratio divides Qty by 3 as a float and is not stored. An index sits on LineTotal. The demo creates dbo.OrderLine and removes it at the end.

DROP TABLE IF EXISTS dbo.OrderLine;

CREATE TABLE dbo.OrderLine (
    ItemId int PRIMARY KEY,
    Qty int NULL,
    UnitPrice decimal(10,2),
    LineTotal AS Qty * UnitPrice PERSISTED,
    Ratio AS CONVERT(float, Qty) / 3
);
CREATE INDEX IX_OrderLine_Total ON dbo.OrderLine (LineTotal);

INSERT dbo.OrderLine (ItemId, Qty, UnitPrice) VALUES (1, 2, 12.50), (2, NULL, 12.50);
SELECT ItemId, Qty, UnitPrice, LineTotal, Ratio FROM dbo.OrderLine ORDER BY ItemId;

Row 1 gives a LineTotal of 25.00 and a Ratio of 0.66666666666666663. Row 2 has a NULL quantity, so both columns are NULL. That is honest. SQL Server does not invent a zero.

Read the definition from the catalog

The view sys.computed_columns holds the expression and the persisted flag. Join it to the tables, schemas and types, add COLUMNPROPERTY for determinism and precision, and join to the index tables so unindexed columns still show up. Remove the WHERE line to inventory a whole database.

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, c.name AS column_name,
       c.definition, c.is_persisted, ty.name AS type_name, c.precision, c.scale,
       COLUMNPROPERTY(c.object_id, c.name, 'IsDeterministic') AS is_deterministic,
       COLUMNPROPERTY(c.object_id, c.name, 'IsPrecise') AS is_precise,
       i.name AS index_name, ic.key_ordinal, ic.is_included_column
FROM sys.computed_columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.indexes AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE c.object_id = OBJECT_ID(N'dbo.OrderLine')
ORDER BY c.column_id, i.index_id;

LineTotal comes back as decimal with precision 21 and scale 2. It is persisted, deterministic and precise, and it is in IX_OrderLine_Total as key column 1. Ratio is a float, not persisted, deterministic but not precise, and has no index. Notice the stored definitions. You typed Qty * UnitPrice, and the catalog shows ([Qty]*[UnitPrice]). SQL Server adds brackets and parentheses, so compare like with like.

Why precision decides what you can index

Float math is approximate, and SQL Server will not index an approximate value unless it is stored. Try to index Ratio.

BEGIN TRY
    CREATE INDEX IX_OrderLine_Ratio ON dbo.OrderLine (Ratio);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, LEFT(ERROR_MESSAGE(), 160) AS error_message;
END CATCH;

Error 2799 says the column is imprecise and not persisted. This is why the is_precise and is_persisted columns belong in your documentation. They tell you what is possible before someone tries.

The index adds a rule for every write

An index on a computed column makes every INSERT and UPDATE depend on session settings. Turn off ANSI_WARNINGS and the insert fails, even though nothing is wrong with the data. SSMS has the right settings on, but a connection from another tool may not.

SET ANSI_WARNINGS OFF;
BEGIN TRY
    EXEC (N'INSERT dbo.OrderLine (ItemId, Qty, UnitPrice) VALUES (3, 1, 1.00);');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, LEFT(ERROR_MESSAGE(), 110) AS error_message;
END CATCH;
SET ANSI_WARNINGS ON;

You get error 1934, and the message names ANSI_WARNINGS as the wrong SET option. Persisting a column also has a price. SQL Server maintains the stored value on every write, and an index adds one more structure to maintain.

Script it so it can be rebuilt

A recreation script needs the word AS, the stored definition and the PERSISTED choice. A bare type declaration loses the formula. This query writes the column clause for you. Indexes, constraints and permissions still need their own review.

SELECT QUOTENAME(c.name) + N' AS ' + c.definition
       + CASE WHEN c.is_persisted = 1 THEN N' PERSISTED' ELSE N'' END AS column_clause
FROM sys.computed_columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.OrderLine')
ORDER BY c.column_id;

DROP TABLE IF EXISTS dbo.OrderLine;

The two lines it returns are the clauses for LineTotal with PERSISTED and for Ratio without. Keep rounding, NULL, zero and boundary test values next to the formula. A valid expression is not the same as a correct business rule.

Run the inventory query on one of your own databases and see how many formulas were never written down.

A computed column is not just a type, it is an expression with storage and indexing rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.