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.
-- Create Table with computed column
CREATE TABLE [dbo].[Customer]
[FirstName] [varchar(100)] NULL,
[LastName] [varchar(100)] NULL,
[FullName] AS ([FirstName] + ' ' + [LastName]) PERSISTED
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
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......
Kavya Shree M.
Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator