Wednesday, July 8, 2015

SQL - Index on Computed Columns


What are Computed Columns?
Computed Columns are virtual columns that are not physically stored in the table. The Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.
What is Computed Column Index?
Index on Computed Column is an index on a computed column that is derived from the value of one or more other columns, or certain deterministic inputs.
Computed Columns is a very powerful feature of SQL Server to store calculated values through manipulation on other columns. A computed column is computed from an expression and this expression can use one or more columns in the same table.

To create a computed column, we can use functions, variables, constants, non- computed column names or any combination of all these may be used along with operators.

How does Computed Column Work?
To explain the computed column, we will create a Vendor Master table having a computed column as Contract End Date which will store the calculated output of the Contract Start Date and Contract Period where Contract Period will contain only year’s values.

Use tempdb
Go

--- Create a Vendor Master table
--- Computed Column - ContractEndDate
--- Computed from ContractStartDate and ContractPeriod
CREATE TABLE VendorMaster
    (
                 VendorId Int not null Primary Key,
                 VendorName varchar(30) NULL,
                ContractStartDate Date Not NULL,
                 ContractPeriod int default(1) Not Null,
                 ContractEndDate AS (dateadd(year,ContractPeriod,ContractStartDate)-(1)) PERSISTED
                 );
GO

In the computed column, you are able to see that we have used “PERSISTED” which is the most important for any computed column, because many additional features depend on it. While planning for any computed column, please keep in mind that although a Persisted computed column will reduce overhead for calculations at run time and it will consume more space on disk.

Now, we are inserting the data into the Vendor Master and will see the output of the Vendor Master as shown below:

--- Insert Values into VendorMaster
Insert into VendorMaster (VendorId,VendorName,ContractStartDate,ContractPeriod)
Values
(1, 'Ryan Arjun', '12/4/2014',2),
(2, 'Rosy Gray', '12/15/2013',2),
(3, 'Smith Johnson', '11/6/2013',3)

--- Fetch Records
Select * from VendorMaster

VendorId
VendorName
ContractStartDate
ContractPeriod
ContractEndDate
1
Ryan Arjun
2014-12-04 00:00:00.000
2
2016-12-03 00:00:00.000
2
Rosy Gray
2013-12-15 00:00:00.000
2
2015-12-14 00:00:00.000
3
Smith Johnson
2013-11-06 00:00:00.000
3
2016-11-05 00:00:00.000

To verify the computed column, we are going to update the table where vendor id =1 and set the contract period = 3 and we will see the updated output of computed column in the table as shown below:

--- Update Vendor Master where vendor id =1
--- Set Contract Period = 3
Update VendorMaster
set ContractPeriod=3
where
VendorId=1

--- Fetch Records after update the data
Select * from VendorMaster

VendorId
VendorName
ContractStartDate
ContractPeriod
ContractEndDate
1
Ryan Arjun
2014-12-04 00:00:00.000
3
2017-12-03 00:00:00.000
2
Rosy Gray
2013-12-15 00:00:00.000
2
2015-12-14 00:00:00.000
3
Smith Johnson
2013-11-06 00:00:00.000
3
2016-11-05 00:00:00.000


How to check computed columns is ISINDEXABLE or not?
To check out that computed column is available for indexing or not, we will run the following script with COLUMNPROPERTY as shown below:

--- checkout computed columns for index
--- creation using COLUMNPROPERTY ISINDEXABLE
SELECT
(SELECT
CASE COLUMNPROPERTY( OBJECT_ID('VendorMaster'), 'ContractEndDate','IsIndexable')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END)
AS 'ContractEndDate is Index able?',
GO

ContractEndDate is Index able ?
Yes


How to create index on computed columns?
Now, we have the table having computed column and we can create index on the this table as shown below:

--- Create index on the computed column
CREATE INDEX ind_VendorMaster_ContractEndDate
ON  VendorMaster (ContractEndDate)
GO

---- How to see the indexes in the table
EXEC sp_helpindex VendorMaster

index_name
index_description
index_keys
ind_VendorMaster_ContractEndDate
nonclustered located on PRIMARY
ContractEndDate

Creating indexes on computed columns requires certain conditions to be fulfilled the following requirements are met:
  • Ownership requirements
  • Determinism requirements
  • Precision requirements
  • Data type requirements
  • SET option requirements
For more details, please visit here

At a Glance:

Use tempdb
Go

--- Create a Vendor Master table
--- Computed Column - ContractEndDate
--- Computed from ContractStartDate and ContractPeriod
CREATE TABLE VendorMaster
    (
                 VendorId Int not null,
                 VendorName varchar(30) NULL,
     ContractStartDate DateTime Not NULL,
                 ContractPeriod int default(1) Not Null,
                 ContractEndDate AS (dateadd(year,ContractPeriod,ContractStartDate)-(1)) PERSISTED
                 );
GO

--- Insert Values into VendorMaster
Insert into VendorMaster (VendorId,VendorName,ContractStartDate,ContractPeriod )Values
(1, 'Ryan Arjun', '12/4/2014',2),
(2, 'Rosy Gray', '12/15/2013',2),
(3, 'Smith Johnson', '11/6/2013',3)

--- Fetch Records after insert the data
Select * from VendorMaster

--- Update Vendor Master where vendor id =1
--- Set Contract Period = 3
Update VendorMaster
set ContractPeriod=3
where
VendorId=1

--- Fetch Records after update the data
Select * from VendorMaster

--- checkout computed columns for index
--- creation using COLUMNPROPERTY ISINDEXABLE
SELECT
(SELECT
CASE COLUMNPROPERTY( OBJECT_ID('VendorMaster'), 'ContractEndDate','IsIndexable')
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END)
AS 'ContractEndDate is Indexable ?'
GO

--- Create index on the computed column
CREATE INDEX ind_VendorMaster_ContractEndDate
ON  VendorMaster (ContractEndDate)
GO

---- How to see the indexes in the table
EXEC sp_helpindex VendorMaster

--- drop table
drop table VendorMaster


Conclusion
An index on a computed column can boost our SQL Server query's performance or even implement a complex business rule because computed columns do not store their own values; their values are derived from regular columns.


Type of Indexes in SQL

1 comment:

  1. Informative Article

    CREATE TABLE statement in the script has the following error:

    Msg 206, Level 16, State 2, Line 21
    Operand type clash: date is incompatible with int

    ReplyDelete