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

This is the third article in the series which I am writing on Persisted Columns. I suggest you read following two article first before continuing on this article.

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.

In this article we will see the other side, how persisted column can be expensive as well. Please note, this is not always the case, but it is the other side of the story of the persisted columns.

In earlier article we noticed that when computation is complex, computed columns are expensive to be created at run time. However, in this case we have relatively simpler example and while comparing the cost of the computed column with persisted data is less than non-persisted data. Let us quickly examine the example.

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(800))
GO
CREATE TABLE CompCol_P (ID INT,
FirstName VARCHAR(100),
LastName CHAR(800))
GO
-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName)
SELECT TOP 10000 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 10000 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 (FirstName+' '+LastName)
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.CompCol_P ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Select Comparision
SELECT FullName
FROM dbo.CompCol
WHERE FullName = 'Bob Smith'
GO
SELECT FullName_P
FROM dbo.CompCol_P
WHERE FullName_P = 'Bob Smith'
GO
-- Clean up Database
DROP TABLE CompCol
DROP TABLE CompCol_P
GO


Let us examine the resultset where we are comparing two SELECT statements.

SQL SERVER - Computed Column - PERSISTED and Performance - Part 2 persisted3

It is clear from above example that here column which are persisted are more expensive. In earlier example here we have seen column which was persisted was less expensive. I suggest that you consider different parameters like size of the column, computation logic, number of the rows in consideration before marking the column as persisted.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – Computed Column – PERSISTED and Performance
Next Post
SQLAuthority News – SQL Data Camp, Chennai, July 17, 2010 – A Huge Success

Related Posts

Leave a Reply