SQL Server 2005 Computed Column Is Persisted

sql-server-2005 calculated-columns
hilary picture hilary · May 27, 2009 · Viewed 14.2k times · Source

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?

Answer

Manu picture Manu · May 27, 2009

"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

Related questions

SQL Server - INSERT failed because of 'ARITHABORT'

I use NHibernate and SQL Server 2005 and I have an index on a computed column in one of my tables. My problem is that when I insert a record to that table I get the following error: INSERT failed because …

Read More
Computed column should result to string

Here is a snap of my database. Both col1 and col2 are declared as int. My ComputedColumn currently adds the Columns 1 and 2, as follows... col1 col2 ComputedColumn 1 2 3 4 1 5 Instead of this, my ComputedColumn should join the columns 1 and 2 (includimg the '-' …

Read More
Computed bit column that returns whether another column is null

I try to have this computed column: CREATE TABLE dbo.Item ( ItemId int NOT NULL IDENTITY (1, 1), SpecialItemId int NULL, --I tried this IsSpecialItem AS ISNULL(SpecialItemId, 0) > 0, --I tried this IsSpecialItem AS SpecialItemId IS NOT NULL --Both don't work ) ON […

Read More