Computed Columns in SQL Server

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4306 red flag

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.
Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Posted by: Sheonarayan on: 12/25/2014 | Points: 25
Good article Bandi.

Thanks and keep it up!

Login to post response

Comment using Facebook(Author doesn't get notification)