Computed columns are a powerful feature in SQL Server that allow you to define a column based on an expression or formula. They can be used to perform calculations, concatenate strings, or derive values from other columns in a table. In this article, we will explore the concept of computed columns and their impact on storage and performance.

Before we dive into the details, it is recommended to read the earlier articles in the series to get a complete understanding of computed columns:

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

One of the questions that often arises when working with computed columns is whether creating an index on a computed column increases the data size of the original table and makes the computed column persisted. The answer is no. When an index is created on a computed column, it does not increase the data size of the original table, nor does it make the computed column persisted.

To illustrate this behavior, let’s consider the following example:

USE tempdb

-- Create Table
CREATE TABLE CompCol (
    ID INT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100)
)

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

-- Check the space used by table
EXEC sp_spaceused 'CompCol'

-- Add Computed Column
ALTER TABLE dbo.CompCol
ADD FullName AS (FirstName + ' ' + LastName)

-- Check the space used by table
EXEC sp_spaceused 'CompCol'

-- Create non-clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_FullName ON dbo.CompCol (FullName)

-- Check the space used by table
EXEC sp_spaceused 'CompCol'

-- Add Computed Column PERSISTED
ALTER TABLE dbo.CompCol
ADD FullName_P AS (FirstName + ' ' + LastName) PERSISTED

-- Check the space used by table
EXEC sp_spaceused 'CompCol'

-- Clean up Database
DROP TABLE CompCol

In the above example, we start by creating a table called “CompCol” and inserting 100,000 records into it. We then add a computed column called “FullName” which concatenates the “FirstName” and “LastName” columns. After checking the space used by the table, we create a non-clustered index on the computed column and observe that it does not increase the data size of the table. Finally, we add another computed column called “FullName_P” and mark it as persisted. Again, checking the space used by the table confirms that only persisted computed columns take additional space.

From this example, it is evident that creating an index on a computed column does not increase the data size of the original table. The index simply utilizes the unused space in the table. This can be a valuable optimization technique when working with large datasets and performance is a concern.

In conclusion, computed columns are a powerful feature in SQL Server that can enhance your database design and improve query performance. By understanding their impact on storage and performance, you can make informed decisions when utilizing computed columns in your database schema.

Thank you for reading this article. We hope you found it informative and helpful. Please feel free to share your thoughts and opinions on this series.