What is computed column and persistent column in sql? How to use them? [Resolved]

Posted by Allemahesh under Sql Server on 7/22/2013 | Points: 10 | Views : 1397 | Status : [Member] [MVP] | Replies : 1
What is computed column and persistent column in sql? How to use them?


Posted by: Kmandapalli on: 7/23/2013 [Member] Silver | Points: 50



For such scenarios where calculated values are required or values are generated through manipulation on other columns, we have a powerful feature provided in SQL Server. This feature is "Computed Columns ".

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.

USE DataBaseName
-- Create Table with computed column
CREATE TABLE [dbo].[Customer]
[FirstName] [varchar(100)] NULL,
[LastName] [varchar(100)] NULL,
[FullName] AS ([FirstName] + ' ' + [LastName]) PERSISTED

USE DatabaseName
INSERT INTO Customer(FirstName, LastName)
SELECT 'Mallareddy', 'Amireddy' UNION ALL
SELECT 'Govind', 'Kuna' UNION ALL
SELECT 'Prabhu', 'Bommareddy'' UNION ALL
SELECT 'Kavya', 'Mandapalli' UNION ALL
SELECT * FROM dbo.Customer

USE Database

SELECT * from dbo.Customer

When you execute this command, you can find your table in this way:
FirstName LastName FullName
Mallareddy Amireddy Mallareddy Amireddy
Govind Kuna Govind Kuna
Prabhu Bommareddy Prabhu Bommareddy
Kavya Mandapalli Kavya Mandapalli

We have used the property "Persisted" for the Computed Column, this property has been introduced from SQL SERVER 2005.It is important for any computed column, because many additional features depend on it. To be able to make a computed column as Persisted it has to be deterministic.

1. If Persisted property is off then calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk.

2. Any update in referenced column will be synchronized automatically in computed column if it is Persisted.

Mark as Answer if satisfied......

Thank You,
Kavya Shree M.

Kavya Shree Mandapalli

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response