Computed columns in SQL Server can have a significant impact on performance. In previous articles, we discussed the storage and performance implications of computed columns, as well as the benefits of using persisted columns and creating indexes on computed columns.

In this article, we will explore a new approach to further enhance the performance of computed columns. We will create a non-clustered index on a computed column and compare its performance with a computed column without an index.

First, let’s create two identical tables and populate them with the same data:

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 VARCHAR(100)
)
GO

CREATE TABLE CompCol_P (
    ID INT,
    FirstName VARCHAR(100),
    LastName VARCHAR(100)
)
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

Next, we will add a computed column to the first table and create a non-clustered index on it:

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

-- Create non-clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_CityTrim ON CompCol (FullName)
GO

Similarly, we will add a computed column to the second table without creating an index:

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

Now, let’s compare the performance of both computed columns:

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

By enabling the actual execution plan, we can observe that creating an index on the computed column significantly improves performance, regardless of whether the column is persisted or non-persisted. However, it’s important to note that having too many indexes can negatively impact system performance.

In conclusion, if you require optimal performance from a computed column, creating a non-clustered index on the column is a surefire way to achieve it. However, it’s crucial to strike a balance between performance optimization and maintaining an efficient system.

Feel free to share your thoughts and experiences with computed columns in the comments section below. We look forward to hearing from you!