How To Index Computed Column In SQL Server

Posted October 27, 2019 by Vishwanath Dalvi in Database, SQL Server

How To Index Computed Column In SQL Server

The computed column also referred to as calculated or generated column. Derived from other column value or an expression. This tech-recipe post inlined with published post How To Use Computed Column In SQL Server. Refer the earlier post as a beginners’ guide to a computed column in SQL Server.

Computed column can be non-persisted or persisted. Adding an index on the computed column can improve query performance. Persisting a computed column occupies disk space. However, on the other hand, we get a speed advantage. Speed benefit as a computed column is not calculated while querying because it is already computed and stored.

In this tech-recipe post, we will learn how to add an index on computed columns to speed up the queries. By comparing non-index and indexed computed columns with IO statistics to learn the performance benchmarks.

1.

Requirements for Adding Index on Computed Column

Before we think of adding an index on computed columns, need to meet the following requirements. SQL server forces to follow the certain rule by design to add an index on computed columns.

1. Column value should be Deterministic

Computed column value should be deterministic means it should always return the same value if fetched more than once. Adding index on a computed column having always changing DateTime values is not permitted. Therefore, the computed persisted column makes it compulsory to have deterministic values.

2. Computed value of a column should be Precise

A computed column should have precise value, means excluding float and real data type values.

3. Computed column data type constraint

Adding a computed column on text, ntextand image data type column is not allowed.

4. SET Options must be ON

SET options in SQL Server including ANSI_NULLS must be on while creating or altering a table having computed columns.

2.

Demo & Sample Scripts

Let’s create employee_1 and employee_2 table with random data. Employee_1 holds non persisted computed column (Yearly_Salary_Computed). Furthermore, employee_2 holds persisted computed column (Yearly_Salary_Computed_Persisted).

DROP TABLE IF EXISTS dbo.Employee_1; --Non-Persisted Column
DROP TABLE IF EXISTS dbo.Employee_2; --Persisted Column

CREATE TABLE dbo.Employee_1

(
   ID			       INT		PRIMARY KEY IDENTITY(1,1)
  ,First_Name		       VARCHAR(255)     NOT NULL
  ,Last_Name		       VARCHAR(255)	NOT NULL
  ,Monthly_Salary	       DECIMAL(18,2)    NOT NULL 
  ,Yearly_Salary_Computed  AS  (Monthly_Salary * 12)	  
);

CREATE TABLE dbo.Employee_2
(
   ID				     INT	   PRIMARY KEY IDENTITY(1,1)
  ,First_Name			     VARCHAR(255)  NOT NULL
  ,Last_Name			     VARCHAR(255)  NOT NULL
  ,Monthly_Salary		     DECIMAL(18,2) NOT NULL 
  ,Yearly_Salary_Computed_Persisted  AS (Monthly_Salary * 12)	PERSISTED  
);

DECLARE @Num AS INT = 1;
DECLARE @NumConvert AS VARCHAR(50);
DECLARE @RandonSalary AS DECIMAL(18,2);

WHILE (@Num <= 10000)
BEGIN
	SET @NumConvert = CAST(@Num AS VARCHAR(50));
	SET @RandonSalary = CAST(@Num AS DECIMAL(18,2)) * ABS(CHECKSUM(NEWID()) % 10000.0)
	INSERT INTO dbo.Employee_1 (First_Name, Last_Name, Monthly_Salary) VALUES ('Bob -' + @NumConvert ,'Porto -' + @NumConvert, @RandonSalary);
	INSERT INTO dbo.Employee_2 (First_Name, Last_Name, Monthly_Salary) VALUES ('Bob -' + @NumConvert ,'Porto -' + @NumConvert, @RandonSalary);

	SET @Num = @Num + 1;	
END


We need to check if we can add an index on both the computed columns. To verify, check if computed columns are deterministic, precise and indexable using the following query.

--Employee_1 properties
SELECT COLUMNPROPERTY( OBJECT_ID('dbo.Employee_1'),'Yearly_Salary_Computed','IsIndexable')		AS 'IsIndexable'
      ,COLUMNPROPERTY( OBJECT_ID('dbo.Employee_1'),'Yearly_Salary_Computed','IsPrecise')		AS 'IsPrecise'
      ,COLUMNPROPERTY( OBJECT_ID('dbo.Employee_1'),'Yearly_Salary_Computed','IsDeterministic')	AS 'IsDeterministic';  

select  t.name as TableName
       ,c.name ColumnName
       ,c.is_computed as IsComputed --1 (Yes)/ 0 (No)
       ,ISNULL(ct.is_persisted,0) as IsPersisted --1 (Yes)/ 0 (No)
from    sys.tables as t INNER JOIN sys.columns as c on	(t.object_id = c.object_id)
	                LEFT JOIN sys.computed_columns as ct on (ct.name = c.name)
where   t.name = 'Employee_1';


How To Index Computed Column In SQL Server_1

--Employee_2 properties
SELECT COLUMNPROPERTY( OBJECT_ID('dbo.Employee_2'),'Yearly_Salary_Computed_Persisted','IsIndexable')		AS 'IsIndexable'
      ,COLUMNPROPERTY( OBJECT_ID('dbo.Employee_2'),'Yearly_Salary_Computed_Persisted','IsPrecise')		AS 'IsPrecise'
      ,COLUMNPROPERTY( OBJECT_ID('dbo.Employee_2'),'Yearly_Salary_Computed_Persisted','IsDeterministic')	AS 'IsDeterministic';  

select  t.name as TableName
       ,c.name ColumnName
       ,c.is_computed as IsComputed --1 (Yes)/ 0 (No)
       ,ISNULL(ct.is_persisted,0) as IsPersisted --1 (Yes)/ 0 (No)
from    sys.tables as t INNER JOIN sys.columns as c on	(t.object_id = c.object_id)
	                LEFT JOIN sys.computed_columns as ct on (ct.name = c.name)
where   t.name = 'Employee_2';


How To Index Computed Column In SQL Server_2

3.

Computed Column – Without Index

Using the following query and listed statistics shows computed column takes overhead to fetch the result without having an index. Without index computed column does not get any speed benefit to fetch the result quickly.

SET STATISTICS IO ON
SET STATISTICS TIME ON
select ID, Yearly_Salary_Computed  from dbo.Employee_1  WHERE Yearly_Salary_Computed >= 10000.0; -- Non-Persisted Computed Columns
select ID, Yearly_Salary_Computed_Persisted from dbo.Employee_2  WHERE Yearly_Salary_Computed_Persisted >= 10000.0; -- Persisted Computed Columns
SET STATISTICS IO OFF
SET STATISTICS TIME OFF


How To Index Computed Column In SQL Server_3

4.

Created Index On Computed Column

Using the following script we are adding an index on non persisted & persisted column in employee_1 and employee_2 table.

CREATE NONCLUSTERED INDEX IX_Employee_YearlySalaryComputed  ON dbo.Employee_1 (Yearly_Salary_Computed);  
CREATE NONCLUSTERED INDEX IX_Employee_YearlySalaryPersisted ON dbo.Employee_2 (Yearly_Salary_Computed_Persisted); 


5.

Computed Column – With Index

Using the following query and listed statistics shows computed column fetches the result quickly based on CPU and IO statistics. Having index on the computed column is beneficial to fetch the result quickly.

SET STATISTICS IO ON
SET STATISTICS TIME ON
select ID, Yearly_Salary_Computed  from dbo.Employee_1  WHERE Yearly_Salary_Computed >= 10000.0; -- Non-Persisted Computed Columns
select ID, Yearly_Salary_Computed_Persisted from dbo.Employee_2  WHERE Yearly_Salary_Computed_Persisted >= 10000.0; -- Persisted Computed Columns
SET STATISTICS IO OFF
SET STATISTICS TIME OFF


How To Index Computed Column In SQL Server_4

Summary

This tech-recipes post covers indexing computed columns in SQL Server. We have learned Non persisted and persisted computed columns and how beneficial adding an index on these columns. How both works differently and storage space allocation. If you like this article, you may want to read through other learning stuff in database posted on Tech-Recipes Database archives.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply