Why does sql server recalculate a persisted compute column at read?

GokhanVarol 2013-06-21 14:25:52

I noticed even though I persisted a compute column that gets calculated on a select statement, I would have thought persisting column would be more efficient since it's already storing data it would not need to recalculate it's values?
Thanks

link text

GokhanVarol 2013-06-21 20:19:42
By the way it does not calculate on select if the calculated column is not nullable.
SQLkiwi 2013-06-24 10:28:04
The optimizer considers both options:

  1. Read the persisted computed column from storage; or
  2. 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;