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.
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)