Why does sql server recalculate a persisted compute column at read?
- Read the persisted computed column from storage; or
- Recalculate the computed column expression from referenced columns
Reading an extra column from the underlying table is not for free, but it is true that the optimizer's costing component does not contain much logic to assess the cost of scalar computations, so the choices made by the optimizer can seem rather arbitrary at times. The details are complex and undocumented, but the nullability of the computed column is not a determining factor.
Using a nullable CASE expression tends to result in the expression being recomputed rather than a persisted column being read, but this is observed behaviour that does not hold in all cases, and not guaranteed.
CREATE TABLE T ( c1 int NULL, c2 AS c1 + 1 PERSISTED, -- Nullable c3 AS ISNULL(c1 + 1, 0) PERSISTED, -- Not nullable c4 AS CASE WHEN c1 IS NULL THEN 0 ELSE c1 END ); INSERT T (c1) VALUES (NULL); SELECT c1, c2, c3, c4 FROM T;