Welcome to the third article in our series on Persisted Columns in SQL Server. If you haven’t read the previous articles, I recommend you start with those before continuing with this one. Here are the links to the previous articles:

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

In this article, we will explore the other side of the story of persisted columns. While earlier articles discussed the benefits of using persisted columns, it is important to note that there are cases where persisted columns can be expensive as well. Although this is not always the case, it is essential to understand the potential drawbacks of using persisted columns.

Let’s take a look at a simple example to illustrate this concept:

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 Comparison
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

In the above example, we create two tables, CompCol and CompCol_P, and insert 10,000 records into each table. We then add a computed column, FullName, to the CompCol table, and a persisted computed column, FullName_P, to the CompCol_P table.

When we compare the two SELECT statements, it becomes clear that the persisted column is more expensive in this particular scenario. However, it is important to note that the cost of using persisted columns can vary depending on factors such as the size of the column, the computation logic, and the number of rows in consideration. Therefore, it is crucial to carefully evaluate these parameters before deciding to mark a column as persisted.

Thank you for reading this article on understanding persisted columns in SQL Server. Stay tuned for more articles in this series where we will continue to explore different aspects of computed columns and their impact on performance and storage.