Index on computed column

Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.

Create index on computed column

Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.

Below is the function being used to create a computed column:

CREATE FUNCTION dbo.fn_GetTotalCourseFee
(
 @CourseFee NUMERIC(9, 2),
 @TaxPercentage NUMERIC(9, 2)
)
RETURNS NUMERIC(9, 2)
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @TotalFee NUMERIC(9, 2) = 0.0
 SET @TotalFee = @CourseFee + (@CourseFee * (@TaxPercentage / 100))
 RETURN @TotalFee
END

Note that the above function is created with schema bind clause “WITH SCHEMABINDING” which is must required to allow indexing on the computed column, otherwise it will throw the below error at the time of index creation:

Msg 2729, Level 16, State 1, Line 32
Column ‘TotalAmount’ in table ‘tbl_CourseDetail’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

Below is the table which uses the function “fn_GetTotalCourseFee” to create a computed column:

CREATE TABLE tbl_CourseDetail
(
 CourseId INT IDENTITY(1, 1) NOT NULL,
 CourseName VARCHAR(100) NOT NULL,
 CourseFee NUMERIC(9, 2) NOT NULL,
 TaxPercentage NUMERIC(9, 2) NOT NULL,
 TotalAmount AS dbo.fn_GetTotalCourseFee(CourseFee, TaxPercentage)
)

To check that the indexes can be created or not on this column, use below t-sql statement:

SELECT COLUMNPROPERTY(OBJECT_ID('tbl_CourseDetail'), 'TotalAmount', 'IsIndexable') AS IsIndexableColumn

Above query returns 1 if the column is indexable otherwise 0 will be returned. Keep in mind that in case the function is not schema bind, it will output 0 here.

As this column supports indexing, to create an index, use CREATE INDEX command as we do in case of regular columns:

CREATE NONCLUSTERED INDEX cidx_tbl_CourseDetail_TotalAmount ON tbl_CourseDetail(TotalAmount)

We have defined a computed column using an user defined function and also created an index on it. Have a look on the below image:

Now, let’s discuss few important prerequisites which must be satisfied to enable indexing on a computed column.

Indexable computed column – Prerequisites

Below are the few important prerequisites we are going to discuss:

1. Determinism requirement
2. Precision requirement

Let’s discuss each point in detail.

Determinism requirement

An expression is said to be deterministic if it returns the same output for a given set of values. To explore more on deterministic and non-deterministic functions, visit my previous post “Deterministic and non-deterministic functions in SQL Server“.

We have created the function fn_GetTotalCourseFee with WITH SCHEMABINDING clause to make it deterministic. If we don’t mark the function as schema binded, it is always non-deterministic and hence the computed column will not be indexable. To check the determinism property of a computed column, use below code:

SELECT COLUMNPROPERTY(OBJECT_ID('tbl_CourseDetail'), 'TotalAmount', 'IsDeterministic') AS IsDeterministic 

Precision requirement

A computed column is precise if it is not generated from the expression of float or real data type i.e. computed columns with float and real expressions are imprecise and can not be indexed.

To check whether a computed column is precise or imprecise, use below code:

SELECT COLUMNPROPERTY(OBJECT_ID('tbl_CourseDetail'), 'TotalAmount', 'IsPrecise') AS IsPrecise 

To read full prerequisites, visit this MSDN link:

Space comparison: Computed column vs indexed computed column

To compare the space being used by the table, let’s insert few rows. Use below script to insert dummy rows in the table and remember that the size and the number of rows might vary on your machine as I am using sys.columns system view to multiply the number of rows in this row generation logic.

INSERT INTO tbl_CourseDetail
(CourseName, CourseFee, TaxPercentage)
SELECT TOP 100000
 CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65)
 + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65)
 + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) AS CourseName,
 CAST(CAST((ABS(CHECKSUM(NEWID())) % 10) AS CHAR(1)) + CAST((ABS(CHECKSUM(NEWID())) % 10) AS CHAR(1)) + CAST((ABS(CHECKSUM(NEWID())) % 10) AS CHAR(1)) 
 AS INT) * 100 AS CourseFee,
 (ABS(CHECKSUM(NEWID())) % 20) AS TaxPer
FROM sys.columns A
CROSS JOIN sys.columns B

Use below command to get the details of spaces being used by the table with index:

SP_SPACEUSED 'tbl_CourseDetail'

Now drop the index and get the details of the spaces being used by the table without index:

DROP INDEX cidx_tbl_CourseDetail_TotalAmount ON tbl_CourseDetail
GO
SP_SPACEUSED 'tbl_CourseDetail'

Have a look on both the images which displays the details of used spaces by the table with and without index. I have highlighted the space used by the index in the first image.

Thanks for the reading and please keep sharing and rating the post. Also don’t forget to put your valuable inputs in comment section.