Index on computed column 2


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:

Index on computed column

Index on computed column

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'
Space used by the table with index

Space used by the table with index

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'
Space used by the table without index

Space used by the table without index

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.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

2 thoughts on “Index on computed column

  • Vishva deepak

    Dear Gopal,
    i like this post and adding move value to my knowledge. thank you for nice post.
    just want to know about the cost of the reindexing specially when table is transactional.
    table is used for upload and reporting both. Please guide me how to automate or scheduled this index to update.

    Regards
    Vishva D. Tripathi

    • Gopal Krishna Ranjan
      Gopal Krishna Ranjan Post author

      Hi Vishva Deepak,
      Thanks for your valuable input. Please find my inline comments:

      *******just want to know about the cost of the reindexing specially when table is transactional.*******
      I think, you should always detect the fragmentation level before reorganizing or rebuilding your index. What MS recommends; 1) if fragmentation level is > 5% and < = 30% then REORGANIZE the index, 2) if fragmentation level > 30% then rebuild the index.
      You can use ONLINE = ON mode with index REBUILD to achieve higher availability.

      *******table is used for upload and reporting both. Please guide me how to automate or scheduled this index to update.*******

      You can use a maintenance plan or some custom scripts. Visit this link for the custom script:
      http://www.sqlrelease.com/rebuild-indexes-dynamically-for-all-databases-having-fragmentation-level-more-than-defined-level