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.
- It cannot be part of INSERT/UPDATE statement.
- 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!