Menu
I have some computed columns in a table and need to know if I should set Is Persisted to true. What are the advantages? Are there any disadvantages? What does 'Is Persisted' mean?
474
Computed columns can be persisted. It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.
A persisted computed column is one that is physically stored in the table. If you don't specify that it's persisted, then the column's value will be calculated each time you run a query against it. You can query the sys. computed_columns system catalog view to find out whether a computed column is marked as persisted.
Some Limitations. You can not reference columns from other tables for a computed column expression directly. You can not apply insert or update statements on computed columns.
A Computed Column cannot be used in a DEFAULT, FOREIGN KEY or NOT NULL constraints. If the expression that is used to define the Computed Column value is deterministic, the Computed Column can be involved in a PRIMARY KEY or UNIQUE constraint.
"Persisted" means "stored physically" in this context.
It means that the computed value is computed once on insert (and on updates) and stored on disc, so it does not have to be computed again on every select.
Persisted also causes a performance penalty on insert and updates, since the column must be computed, but will increase the performance on subsequent select queries.
So, it depends on your usage pattern, which approach to follow: if you update infrequently, but query a lot, you should set persisted = true.
If you update frequently, or if you do not care about retrieval performance, you should consider setting persisted = false
147
One more thing not mentioned in other answers: a computed column must be PERSISTED to be usable from FOREIGN KEYs.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
