As a SQL Server developer, you may often come across situations where you need to improve the performance of your queries. One way to achieve this is by using computed columns in your database tables. In this article, we will explore how computed columns can be used to enhance query performance.

Computed columns are virtual columns that are not physically stored in the database. Instead, their values are calculated on the fly based on the expressions or functions defined during column creation. These columns can be used in queries just like regular columns, but they offer the advantage of improved performance when properly utilized.

Let’s consider an example to understand how computed columns can enhance performance. Suppose we have a table called “CompCol” with columns like “ID”, “FirstName”, “LastName”, and “BirthDate”. Initially, when we query the table without any indexes, we may experience a table scan, which can be time-consuming for large datasets.

To improve the performance, we can create an index on the “BirthDate” column. However, even with the index, we may still encounter an index scan instead of an index seek due to the computation involved in the query. This is where computed columns come into play.

We can create a computed column called “BirthMonth” that stores the month value extracted from the “BirthDate” column. By creating an index on this computed column, we can significantly improve the performance of queries that involve filtering by birth month.

After creating the computed column and the index, we can observe that the index scan is converted to an index seek, resulting in faster query execution. This demonstrates the effectiveness of computed columns in enhancing performance.

It’s important to note that computed columns are materialized at runtime, which means they don’t provide a performance boost on their own. However, when combined with an index, they can greatly improve query performance.

Here is the code snippet that demonstrates the steps involved in creating and utilizing computed columns:

USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompCol]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CompCol]
GO
CREATE TABLE [dbo].[CompCol](
[ID] [int] NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](100) NULL,
[BirthDate] [datetime] NULL,
CONSTRAINT [PK_CompCol] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO

-- Insert One Hundred Thousand Records
INSERT INTO CompCol (ID,FirstName,LastName, BirthDate)
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,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN DATEADD(yy,-2, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%3 = 1 THEN DATEADD(yy,-3, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%5 = 1 THEN DATEADD(yy,-5, '2010-08-13 14:20:24.853')
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%7 = 1 THEN DATEADD(yy,-7, '2010-08-13 14:20:24.853')
ELSE GETDATE() END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO

-- Create non-clustered index on regular column
CREATE NONCLUSTERED INDEX IX_CompCol_BrithDate
ON dbo.CompCol (BirthDate, FirstName)
GO

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO

-- Add Computed Column
ALTER TABLE dbo.CompCol ADD
BirthMonth AS MONTH(BirthDate)
GO

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO

-- Create non-clustered index on Computed Column
CREATE NONCLUSTERED INDEX IX_CompCol_BirthMonth
ON dbo.CompCol (BirthMonth, FirstName)
GO

-- Select specific year data
SELECT ID, FirstName
FROM CompCol
WHERE BirthMonth = 8
GO

-- Compare above query with original Query
SELECT ID, FirstName
FROM CompCol
WHERE MONTH(BirthDate) = 8
GO

-- Compare above query with original Query
-- with hint of original index use
SELECT ID, FirstName
FROM CompCol WITH (INDEX(IX_CompCol_BrithDate))
WHERE MONTH(BirthDate) = 8
GO

-- Clean up Database
DROP TABLE CompCol
GO

In conclusion, computed columns combined with appropriate indexes can significantly enhance the performance of your SQL Server queries. By leveraging the power of computed columns, you can optimize your database design and improve the overall efficiency of your applications.