Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to create indexes on computed columns in SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > Indexing

Attend a SQL Server Conference for FREE >> click to learn more


Problem
In my recent article Using Computed Columns in SQL Server with Persisted Values I discussed how to create computed columns to improve performance in specific scenarios.  In order to achieve maximum performance through computed columns one very important aspect that can also be implemented is creating indexes on these computed columns. There are certain requirements for creating indexes on computed columns and this tip shows you want needs to be done.

Solution
First we will create a table with the required specifications, so that indexes may be created on the computed columns.

The following script#1 will create a UDF to be used for the computed column and a table named CCIndexTest with computed columns in it.

Script # 1: Create UDF to use in computed column expression and create table with computed columns and insert sample data

USE [AdventureWorks]
GO

-- Create UDF to use in computed column expression
CREATE FUNCTION
[dbo].[UDF_CalculatePay] ( @basicPay INT, @BonusPercentage TINYINT, @TaxPercentage TINYINT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @TotalPay INT
SET @TotalPay = @basicPay + @basicPay*@bonusPercentage/100 - @basicPay*@taxPercentage/100
RETURN @TotalPay
END
GO

 

IF OBJECT_ID('CCIndexTest', 'U') IS NOT NULL
DROP TABLE CCIndexTest
GO

-- Create table CCIndexTest with two computed columns
CREATE TABLE [dbo].[CCIndexTest](
[EmpNumb] [INT] NOT NULL,
[DOBirth] [DATETIME] NULL,
[DORetirement] AS (DATEADD(YEAR,(60),[DOBirth])-(1)) PERSISTED,
[BasicPay] [SMALLINT] NULL,
[BonusPercentage] [TINYINT] NULL,
[TaxPercentage] [TINYINT] NULL,
[TotalPay] AS [dbo].[UDF_CalculatePay] ( basicPay, BonusPercentage, TaxPercentage)
) ON [PRIMARY]
GO

-- Insert sample data
INSERT INTO dbo.CCIndexTest (empNumb, DOBirth, BasicPay, BonusPercentage, TaxPercentage)
SELECT 30 ,'1985-12-13', 16000, 10, 3 UNION ALL
SELECT 25 ,'1980-11-18', 17000, 12, 3 UNION ALL
SELECT 21 ,'1978-01-19', 16500, 15, 3 UNION ALL
SELECT 7 ,'1985-11-13', 18600, 10, 3 UNION ALL
SELECT 51 ,'1975-07-23', 22300, 15, 3 UNION ALL
SELECT 55 ,'1973-06-21', 21200, 20, 3
GO

-- Select data from dbo.CCIndexTest
SELECT * FROM dbo.CCIndexTest
GO

Now we have a sample table with two computed columns. Before going into the details of the different requirements or prohibited properties we will simply check our computed columns for index creation using COLUMNPROPERTY ISINDEXABLE

Script#2: Check computed columns for index creation

SELECT
(SELECT
CASE COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'), 'DORetirement','IsIndexable')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END)
AS 'DORetirement is Indexable ?',
(
SELECT
CASE COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'),'TotalPay','IsIndexable')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END)
AS 'TotalPay is Indexable ?'

GO

Above script just mentions that either a given column is indexable or not.

Here are the results from the above query.

Both of our computed columns are indeaxble. Now we may discuss the properties that make a computed column indexable or not.

Indexes can be created on computed columns in SQL Server 2000 and onwards. In case of SQL Server 2000 there are no persisted computed columns, so some of the rules for creating indexes on computed columns in SQL Server 2000 differ slightly where persistence is involved. There are two important requirements that may need planning and analysis while creating indexes on computed columns in SQL Server

  1. Determinism requirements
  2. Precision requirements

Following we will elaborate these requirements while planning to create indexes on computed columns.

Determinism Requirements

Keeping in mind the main concept associated with the property DETERMINISTIC, we have to make sure that the expression of our computed column is always the same for specific inputs. This check can easily be performed by using the COLUMNPROPERTY function ISDETERMINISTIC on our computed columns as follows:

Script#3: Check determinism for computed columns

USE AdventureWorks;
GO

SELECT
(
SELECT CASE
COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'),'DORetirement','IsDeterministic')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
) AS 'DORetirement is Deterministic ?',

(
SELECT CASE
COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'),'TotalPay','IsDeterministic')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
)AS 'TotalPay is Deterministic ?'
GO

Here are the results from the above query.

Both of these computed columns are deterministic.

Also we can check the UDF (udf_CalculatePay) used in the TotalPay computed column to see that this is also deterministic.

Script#4: Check determinism for UDF

USE adventureworks
GO

SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[udf_calculatePay]'),
'IsDeterministic') IsUDFDeterministic
GO

Result in this case is 1, indicating that the UDF is deterministic as well.

It is important to note that if your UDF does not return non-deterministic data and also it returns a precise data type, you still need to create it with SCHEMA BINDING to make it deterministic. In our case UDF is SCHEMABINDED hence it is deterministic and the computed column that is accessing it is also deterministic and indexable.

If the UDF is not SCHEMABINDED then it will be always non-deterministic and the computed column accessing it will not be deterministic hence it won't be indexable. Also it is a good practice to make your UDFs schema bound when they are used by a omputed column. Also,  note that a non-deterministic column can not be persisted.

Generally a computed column will be deterministic if it has one or more of  the following properties:

  • All user defined functions or built in functions referenced in computed column expression are deterministic and precise.
  • Columns referenced in computed column expression come from the table containing computed column.
  • Computed column does not pull data from multiple rows of a column. This may be the case when using aggregate functions in computed column expression.
  • Computed column has no system or user data access. It will be the case when you are using a system or user defined function. In our case we may verify this for our UDF [dbo].[udf_calculatePay].  This can be checked using the following script
Script#5: Check that UDF access user data or system catalog

Use AdventureWorks
GO

SELECT
(
SELECT CASE
OBJECTPROPERTYEX(OBJECT_ID('dbo.udf_calculatePay'), 'SYSTEMDATAACCESS')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
) AS 'UDF Accesses system catalog ?' ,(
SELECT CASE
OBJECTPROPERTYEX(OBJECT_ID('dbo.udf_calculatePay'), 'USERDATAACCESS')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
)AS 'UDF Accesses user data ?'

GO

The properties SYSTEMDATAACCESS and USERDATAACCESS can be applied to views or functions. Result is negative for our both cases

Precision Requirements

Precision of a computed column depends upon the data types that are involved in the expression or UDF used for computed column. A computed column may be deterministic, but not precise. Like that of indexed views, you can use a non-precise data type (real, float) in a computed column, but not as a key. To check the precision property of a computed column you may use following script.

Script#6: Check precision for computed column

USE AdventureWorks;
GO

SELECT
(
SELECT CASE
COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'),'DORetirement','IsPrecise')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
) AS 'DORetirement is Precise ?',

(
SELECT CASE
COLUMNPROPERTY( OBJECT_ID('dbo.CCIndexTest'),'TotalPay','IsPrecise')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
)AS 'TotalPay is Precise ?'
GO

The property ISPRECISE can be applied to a computed column, function, user-defined type or views. Result in our case is as follows:

Both computed columns are precise. If the UDF used in computed column is created as non SCHEMA BINDED then it will be non-precise even if precise data types are used.

Now we have confirmed that two major requirements for creating indexes on computed columns are fulfilled. Hence let us create indexes on both of the computed columns. Both indexes will be non-unique and non-clustered. However you may create indexes with clustered and unique properties.

Script#7: Create indexes on both computed columns

USE AdventureWorks
GO

CREATE INDEX index_DORetirement_CCIndexTest
ON dbo.CCIndexTest
(
DORetirement
)

GO

CREATE INDEX index_TotalPay_CCIndexTest
ON dbo.CCIndexTest
(
TotalPay
)
GO

To check that the indexes were created we can look in SSMS.

Hopefully that explains how to create indexes on computed columns and what properties need to be set.


To cleanup and remove all of these sample objects you can run the following script.

Script#8: Drop table and UDF

USE AdventureWorks
GO

DROP TABLE [dbo].[CCIndexTest]
GO


DROP FUNCTION [UDF_CalculatePay]
GO

 

Next Steps



Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, September 27, 2012 - 4:02:55 PM - nian Back To Top

Thanks. Very helpful. 


Thursday, September 27, 2012 - 12:40:42 PM - Atif Shehzad Back To Top

According to BOL isSystemVerified is a column propety that shows that

the determinism and precision properties of the column can be verified by the Database Engine. This property applies only to computed columns and columns of views.

I think this definiion well describes this property.

Thanks

 


Thursday, September 27, 2012 - 11:51:34 AM - nian Back To Top

This article solved a myth when I am preparing for my MCTS 07-433 exam. The question is:

You have a computed column that is implemented with a user-defined function. The user-defined function returns a formatted account number. The column must be indexed to provide adequate search performance.

You plan to create an index on the computed column. You need to identify the valid combination of ObjectPropertyEX values for the user-defined function.

Which combination should you use? 

The answer is:

IsDeterministic = True

IsSystemVerified = True

UserDataAccess = False

SystemDataAccess = False

These is only one thing left, what is IsSystemVerified? Is it also a requirement for creating index on computed columns?


Learn more about SQL Server tools