[SOLVED] What is the purpose of setting a calculated column to be PERSISTED?

What is the purpose of setting a calculated column to be PERSISTED?

My table with the calcIncome calculated column:

CREATE TABLE foo
(
    id int, 
    name varchar(100),
    [other columns], 
    calcIncome AS dbo.fn_calcIncome() PERSISTED NOT NULL
)

Query:

select * 
from foo
where calcIncome > 100

Even though I set calcIncome to be PERSISTED, this query still calls fn_calcIncome for every single row.

So what exactly is the purpose of setting a calculated column to be PERSISTED if it behaves the same as non-persisted calculated columns?

Solution

From Paul White's article Properly Persisted Computed Columns

Persisted computed columns were added to the product specifically to allow indexes to be built on deterministic but "imprecise" (floating point) columns

So preventing re-evaluation of calculations - including UDFs was not the design goal.

But the article does also mention that there is a trace flag 176 that can help in this respect.

In general with computed columns whether or not the column is marked as persisted in the data page you can index it and then can get an index seek using the value stored in the index. But I have just tested that and wasn't able to get a seek when the index was built on the column referencing a UDF either (without both marking the column as PERSISTED and trace flag 176).

I rarely if ever use computed columns that reference scalar UDFs as they also have implications for parallelism.