Is it possible to create INDEX on computed column? Let’s try out!!!

It is always good to have computed column to improve the performance in specific scenarios. But in order to improve performance one need to implement INDEX on computed, but creating index on computed column is not as simple as on regular column. There are certain requirements for creating INDEX on computed column.

Let’s table a practical scenario to explain the way to create index on computed column:

The below is the UDF which the computed column will take in use while table creation.

create function sp_calulatePay(@basicPay int, @hra int)

returns int

with schemabinding

as

begin

      return (@basicPay + @hra)

end

Let’s create table with two computed column, the totalPay column will be using UDF sp_calulatePay.

create table ComputedIndexTesting

(name varchar(10),

dob datetime,

dobPlusOneYear as (dateadd(year,1,dob)) persisted,

basicPay int,

hra int,

totalPay as dbo.sp_calulatePay(basicPay,hra)

)

Command(s) completed successfully.

Let’s have some sample data inserted to this table

insert into ComputedIndexTesting (name,dob,basicPay,hra)

select 'Radhika','05/02/1986',1000,500 union all

select 'Vedika','05/03/1986',1000,500 union all

select 'Shyam','05/04/1986',1000,500

(3 row(s) affected)

Lets find out the values of computed column by firing the below select query

select * from ComputedIndexTesting

name       dob                     dobPlusOneYear          basicPay    hra         totalPay

---------- ----------------------- ----------------------- ----------- ----------- -----------

Radhika    1986-05-02 00:00:00.000 1987-05-02 00:00:00.000 1000        500         1500

Vedika     1986-05-03 00:00:00.000 1987-05-03 00:00:00.000 1000        500         1500

Shyam      1986-05-04 00:00:00.000 1987-05-04 00:00:00.000 1000        500         1500

(3 row(s) affected)

Now let’s check our computed columns for index creation using COLUMNPROPERTY ISINDEXABLE

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsIndexable')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is Indexable ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsIndexable')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is Indexable ?'

//OUTPUT

dobPlusOneYear is Indexable ? totalPay is Indexable ?

----------------------------- -----------------------

Yes                           Yes

(1 row(s) affected)

The result shows both the column are indexable.

Now here our task starts, there are two important requirements that may need planning and analysis while creating indexes on computed columns in SQL Server

• ·         Determinism requirements
• ·         Precision requirements

Determinism : we have to make sure that the expression of our computed column is always the same for specific inputs. This can be checked by COLUMNPROPERTY function and IsDeterministic

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsDeterministic')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is Deterministic ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsDeterministic')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is Deterministic ?'

dobPlusOneYear is Deterministic ? totalPay is Deterministic ?

--------------------------------- ---------------------------

Yes                               Yes

(1 row(s) affected)

This show both the computed columns are deterministic.

We can also check our UDF for deterministic by:

SELECT objectproperty(object_id('dbo.sp_calulatePay'),

'IsDeterministic') IsUDFDeterministic

IsUDFDeterministic

------------------

1

(1 row(s) affected)

So we can see UDF is deterministic as well.

NOTE: You need to create UDF with SCHEMA BINDING to make it deterministic. And since we have created our UDF with schema binding, it shows it is deterministic in nature.

Precision requirements: This is solely depends on the data type that are involved in your expression or in UDF for deriving values for your computed column. You can use non-precise value in your computed column like float, real but then you can not create index on such computed column.

Now lets see, whether our computed column are Precise in nature.

select 

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'dobPlusOneYear','IsPrecise')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'dobPlusOneYear is IsPrecise ?',

(select

case COLUMNPROPERTY( OBJECT_ID('ComputedIndexTesting'), 'totalPay','IsPrecise')

when 0 then 'No'

when 1 then 'Yes'

end)

as 'totalPay is IsPrecise ?'

dobPlusOneYear is IsPrecise ? totalPay is IsPrecise ?

----------------------------- -----------------------

Yes                           Yes

(1 row(s) affected)

So now we are confirmed that both the computed column are Precise as well as Deterministic, so we can create index on computed column, let create the index on it.

create index index_dobPlusOneYear

on dbo.ComputedIndexTesting

(

dobPlusOneYear

)

Command(s) completed successfully.

create index index_totalPay

on dbo.ComputedIndexTesting

(

totalPay

)

Command(s) completed successfully.

Your index is now created on computed column.

Conclusion: For creating index on computed column, two condition should meet first computed column should be:     

• ·         Deterministic
• ·         Precise

Coding: the straightforward among all SDLC  

Post Reference: Vikram Aristocratic Elfin Share