How to create indexes on computed columns in SQL Server
March 7th 2009 12:49
How to create indexes on computed columns in SQL Server
----------------------------- ----------------------------- ----------------------
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.
DO Retirement is Indexable? TotalPay is Indexable?
YES YES
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
Determinism requirements
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','IsDeterminist ic')
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.
Deterministic?
YES YES
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('d bo.udf_calculatePay'), 'SYSTEMDATAACCESS')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
) AS 'UDF Accesses system catalog ?' ,(
SELECT CASE
OBJECTPROPERTYEX(OBJECT_ID('d bo.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
UDF Accesses system catalog? UDF Accesses user data?
No No
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:
DO Retirement is Precise? TotalPay is Precise?
YES YES
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
----------------------------- ----------------------------- ----------------------
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,
[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.
DO Retirement is Indexable? TotalPay is Indexable?
YES YES
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
Determinism requirements
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','IsDeterminist ic')
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.
Deterministic?
YES YES
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('d bo.udf_calculatePay'), 'SYSTEMDATAACCESS')
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
) AS 'UDF Accesses system catalog ?' ,(
SELECT CASE
OBJECTPROPERTYEX(OBJECT_ID('d bo.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
UDF Accesses system catalog? UDF Accesses user data?
No No
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:
DO Retirement is Precise? TotalPay is Precise?
YES YES
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
| 20 |
| Vote |
Add To: 
