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