A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non-computed column names or any combination of all these may be used along with operators to create a computed column.
Introduction
A computed column is computed from an expression that can use another column or columns in the same table. Functions, variables, constants, non-computed column names or any combination of all these may be used along with operators to create a computed column.
Computed column can be persisted, computed values is stored with the table's data, not computed on the fly. Only deterministic functions are allowed.
Below is the sample script to work with Computed columns in SQL Server.
USE [AdventureWorks]
GO
-- Create Table with computed column
CREATE TABLE [dbo].[ComputedColumnTest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[RetirementDate] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO
-- INSERT DATA
USE AdventureWorks
GO
INSERT INTO CompetedColumnTest (empNumb, DOBirth)
SELECT 1,'1985-12-13' UNION ALL
SELECT 2 ,'1988-10-27' UNION ALL
SELECT 3 ,'1990-01-19' UNION ALL
SELECT 4 ,'1988-12-15' UNION ALL
SELECT 5 ,'1970-08-23'
GO
SELECT * FROM dbo.ComputedColumnTest
GO
The above query results the auto-calculated retirmentDate for each record.
-- Update Data
USE AdventureWorks
GO
UPDATE [ComputedColumnTest]
SET DOBirth = '1950-03-25'
WHERE empnumb = 5
GO
SELECT * FROM dbo.[ComputedColumnTest]
WHERE Empnumb = 5
GO
Advantages
These are very useful whenever we need to do some operations
frequently in the reports
·
If you have newly added columns which have to be populated
dependent of other columns in the table, Triggers are used. To avoid Trigger
overheads, simply use Computed columns
Some Limitations
Limitations on Computed Column:
1. computed column cannot be the target of an INSERT or UPDATE statement
-- Update/Insert Data for Computed Column ?
UPDATE [ComputedColumnTest]
SET [RetirementDate] = '1950-03-25'
WHERE empnumb = 5
--see the below error message
/*
Msg 271, Level 16, State 1, Line 1
The column "RetirementDate" cannot be modified because it is either a computed column or is the result of a UNION operator.
*/
Solution: To overcome this, we need to use DML Trigger
2. we can not specify CHECK, DEFAULT, FK or NOT NULL constraint for a computed column
ALTER TABLE [ComputedColumnTest] ALTER COLUMN [RetirementDate] datetime NOT NULL
Error Message:
/*
Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'RetirementDate' because it is 'COMPUTED'.
*/
3. To create Primary key or Unique key on computed column, that should be DETERMINISTIC
ALTER TABLE [dbo].[ComputedColumnTest] ADD DeterColumn AS [empNumb]*RAND() UNIQUE
GO
/*
Msg 2729, Level 16, State 1, Line 1
Column 'DeterColumn' in table 'dbo.ComputedColumnTest' cannot be used in an index or statistics or as a partition key because it is non-deterministic.
*/
4. Subqueries are not allowed as part of Computed column definition
ALTER TABLE [dbo].[ComputedColumnTest] ADD DeterColumn AS ( SELECT ID FROM EMP WHERE ID=2)
GO
/*
Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
*/
Conclusion
To use a computed column as Primary or Unique Key constraint it should be defined by a deterministic expression and data type of computed column expression should be index able.