SQL SERVER – Computed Column and Performance – Part 3

I am really enjoying writing about computed column and its effect in terms of performance. Before continuing this article, I suggest you read the earlier articles on the same subject to get the complete context. This part looks at computed column and performance when an index is added.

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.

In this article, we will see how we can get an even better performance than all the previously seen solutions. Here we will create a non-clustered index over a computed column. In our example, we will be creating two identical tables and populate them with the same data. Once the data are populated, we will create a computed column with the same definition. Once the column is created, we will create an index on the first computed column, while the second computed column has no index. We will then compare the performance of both computed columns. Here is a clearer view of this procedure:

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
-- 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
-- Add Computed Column
ALTER TABLE dbo.CompCol_P ADD
FullName_P AS (FirstName+' '+LastName)
GO
-- Select Comparision
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

Let us check the resultset by enabling actual execution plan.

We can clearly see that without creating a persisted column, we can still get a better performance if we create the index on the column which was computed. If your SELECT statement is retrieving more data, you may face bookmark lookup; but again, this query will give you a better performance on either persisted or non-persisted computed column. If you really need the performance from a computed column, then this is the sure shot. However, please keep in mind that too many indexes are also not good for your system.

Let me know what you think about the situation. I would like to hear your comments,just like the comment of Marko Parkkola; he has written an excellent comment here.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.