CODEWITHSUNDEEP
sql-server-2005calculated-columns
hilary
hilary

Reputation: 714

SQL Server 2005 Computed Column Is Persisted

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?

Upvotes: 31

Views: 14838

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

One more thing not mentioned in other answers: a computed column must be PERSISTED to be usable from FOREIGN KEYs.

Upvotes: 7

Manu
Manu

Reputation: 29143

"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

Upvotes: 46

Related Questions