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';
--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';
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
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
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.
