SQL SERVER – Computed Column – PERSISTED and Performance

This is the list of the all the articles in the series of computed column.

SQL SERVER – Computed Column – PERSISTED and Storage

This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance

This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2

This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3

This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance

This article summarized all the articles related to computed columns.

Previously, I wrote a blog post on the subject SQL SERVER – Computed Column – PERSISTED and Storage. I promised at the end of the article that I will write about the effect of persisted columns in terms of performance in my future blog posts. In this article, we will see how persisted column is better considering performance.

Before I begin talking about the subject, please remember that I have created an example here where the calculation of the computed column is a bit expensive. And because of that, persisted columns are giving better performance than the cost of reading IO from database.

Let us run the following example first.

USE tempdb
GO
-- Create Table
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompCol]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CompCol]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompCol_P]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CompCol_P]
GO
CREATE TABLE CompCol (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
CREATE TABLE CompCol_P (ID INT,
FirstName VARCHAR(100),
LastName CHAR(8000))
GO
-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
INSERT INTO CompCol_P (ID,FirstName,LastName)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.CompCol_P ADD
FullName_P AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12) PERSISTED
GO
-- Select Comparision
SELECT FullName
FROM dbo.CompCol
WHERE FullName = 531441
GO
SELECT FullName_P
FROM dbo.CompCol_P
WHERE FullName_P = 531441
GO
-- Clean up Database
DROP TABLE CompCol
DROP TABLE CompCol_P
GO

Once you run the code shown above, please enable execution plan while running SELECT statement.

You can clearly see that there is an additional operator when columns are not persisted. In fact, that operation occurs when the column value of the computed column is calculated at run time. Let us compare the final cost of both the operations by reading data from mouse-over pop up.

You can clearly see that in the case of the persisted column, the cost is lower compared to the value of non-persisted column.

When the computation is complex, a persisted column improves performance by pre-calculating the values of computed columns.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – Computed Column – PERSISTED and Performance

  1. Hello Pinal,

    I have a doubt regarding computed column. According to my understanding valud in a persisted computer column will be populated when we perform an INSERT operation on that table (Correct me if i am wrong). So what overhead does the persisted column will have while performing an INSERT operation on the table?

    Regards,
    Taher

  2. Taher – It should be the same as INSERT +(cost of the calculation). It will still save you resources since it is a one-time computation instead of a computation every time you query the data.

  3. System.NullReferenceException
    未将对象引用设置到对象的实例。
    Stack Trace:
    在 Quest.Toad.ConnectionManager.ConnectionControl.get_CanRename()
    在 Quest.Toad.ConnectionManager.ConnectionControl.UpdateActions()
    在 Quest.Toad.ConnectionManager.ConnectionControl..ctor()
    在 Quest.Toad.ConnectionManager.ConnectionManagerControl.InitializeComponent()
    在 Quest.Toad.ConnectionManager.ConnectionManagerControl..ctor()

  4. Hi

    Another consideration is whether or you are using the computed column in an index. Adding a column to an index is one method of persisting, and if you are doing so, it is worth considering whether your queries will use that index.

    I often use a computed column as a hash check (please note, for mission critical applications, don’t JUST do a hash check, do an initial hash check for higher performance, and then do a column check as well. I have encountered one scenario where we actually had 2 rows with different data and the same hash. Unlikely, but possible), and create an index on the computed column.

    In this case, persisting the column will add no benefit, and increase the storage necessary

  5. Pinal,
    I need to add 2 columns to a table (A Body Mass Index column and a Body Surface Area column) both of which have a semi complicated equasion; need to use height and weight in the calculation and both need to be numeric and greater than 0. Unfortunately the height and weight columns are Varchar data types.

    I am torn between using Computed Columns Persisted or Triggers to populate these columns.

    Which method would you suggest I use and why?

    Thanks in advance,
    Mike

  6. Pingback: SQL SERVER – Computed Column and Performance – Part 3 Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Computed Column – PERSISTED and Storage – Part 2 Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – Computed Columns – Index and Performance Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Computed Columns – Index and Performance Journey to SQL Authority with Pinal Dave

  10. Hi,
    I added a calculated column as follow

    ALTER TABLE tBillBandGroup
    ADD [BillBandHash] AS CHECKSUM([BillBand]);
    GO

    CREATE CLUSTERED INDEX [tBillbandGroup_BillBandHash_NU_Nidx] ON [dbo].[tBillBandGroup]
    (
    [BillBandHash] ASC
    )WITH (PAD_INDEX = …
    GO

    and using it as
    – OLD INNER JOIN tCallBandGroup on tCallBand.Name = tCallBandGroup.Band) vBandGroup
    INNER JOIN tCallBandGroup on checksum(tCallBand.Name) = tCallBandGroup.BandHash AND tCallBand.Name = tCallBandGroup.Band) vBandGroup

    I noticed a great improvement as i think the sql skips the second AND (as done in C# in short-circuit AND evaluation)
    Do you think make sense to make checksum() the BillBandHash or the checksum is so fast that the saved space not making it persistent is the best strategy to follow?
    regards, Mario

  11. Try this :
    SELECT FullName
    FROM dbo.CompCol
    WHERE FullName = 531441
    go
    select POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)a from CompCol

    WHERE POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12) = 531441
    GO
    SELECT FullName_P
    FROM dbo.CompCol_P
    WHERE FullName_P = 531441

    See The execution plan

  12. I did a test with SQL 2008 R2 and a persisted column gives the same execution plan as a non-persisted column –> still produces a compute scalar operation
    I personally like the persisted approach for indexing and the fact that it’s only calculated on updates.
    another note: a sub-tree cost of 0.0032868 is a granularity reporting error in the qry optimizer. I’ve see this before in analyzing execution plans

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  17. if i declare a computer column that its value refers to complex function that means in every select doing for this table the function is called e.g.(select * from mytable)
    is this affect the performance for this query?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s