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

I am really enjoying writing about computed column and its effect in terms of storage. Before I go on with this topic, I suggest you read the earlier articles about computed column to get the complete context.

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.

This article actually originates from the questions asked by one of the smartest attendees I have ever met in training a few weeks ago. He really impressed me with these good questions. He asked me this: “if we create the index on computed column, does it increase the data size of the original table? In other words, does the computed column become persisted if we create an index on it?“

This is a great question and the answer is rather very simple: No. It does not increase the data size of the original table as well as it does not turn the column into persisted. When we create an index on the column of the table, there is an additional space occupied by that index. Let us run the following code and understand this behavior.

USE tempdb
GO
-- Create Table
CREATE TABLE CompCol (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName)
SELECT TOP 100000 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
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
FullName AS (FirstName+' '+LastName)
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_FullName
ON dbo.CompCol (FullName)
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.CompCol ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Check the space used by table
sp_spaceused 'CompCol'
GO
-- Clean up Database
DROP TABLE CompCol
GO


Here we have done the following efforts:

1. Basic table and measured the used space
2. Computed column and measured the used space
3. Index on computed column and measured the used space
4. Table and measured the used space

Now let us observe the resultset.

Taking a look at the result, it is very clear that columns made persisted only takes additional spaces. On the other hand, when an index is created, it does not increase the data size of the table; rather, the index created just uses up the unused space in the table.

Let me know your opinion about this series.

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