Computed columns in SQL Server

What are computed columns in SQL Server

Computed columns are virtual columns that does not physically store the data in SQL Server unless it is marked as PERSISTED column. Computed columns can be defined by expression that can use other columns of the same table or static expressions.

Limitations

  1. It cannot be part of INSERT/UPDATE statement.
  2. It cannot defined as FOREIGN KEY/DEFAULT or NOT NULL constraint.

How to add a computed column while creating a table?

Create Table SampleComputedTable
(
	EmpName varchar(50),
	DOB Date,
	Age as Datediff(year,DOB,GETDATE())
)

--Today is -> '2021-07-25'; for simple explantion, the Age is calculated only year part.
Insert into SampleComputedTable Values ('Latheesh','2019-07-25')

Select * From SampleComputedTable

Drop table SampleComputedTable

How to add a computed column to an existing table?

Alter Table SampleComputedTable Add AgeInMonths as DateDiff(month,DOB,Getdate())

Select * From SampleComputedTable

What are the best practices while creating a computed column?

There is NO way to alter computed column. You will have to drop and recreate it. 

How to find the definition of a computed column?

Select object_name(object_id) TableName, name as ColumnName, definition From sys.computed_columns 
where name='Age' and object_name(object_id) = 'SampleComputedTable'

If you enjoyed this blog post, feel free to share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s