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 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
|
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
|
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
|
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
|
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
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.
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
Informative Article
ReplyDeleteCREATE 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