SQL Basics – Persisted and Non-Persisted Computed columns in SQL Server
In SQL Server you have an option to create Calculated Columns in a table other than normal columns. By saying Normal columns I meant that their values are stored physically with the table. But with Calculated Columns their values are not stored physically, instead they are re-calculated every time whenever they are referenced.
A Computed Column is nothing but an expression, which can be a:
1. Non Computed column
4. or any combination of above of these by using operators.
These type of Computed Columns are Non-Persisted in nature.
Persisted Computed Columns: You can also make Computed columns values stored physically by using the PERSISTED keyword. This will calculate the column values initially while inserting a row, or later if there is any change made to the column that is part of its expression.
Finally a Computed Column:
– is computed from an expression that can use other columns in the same table.
– can be a noncomputed column name, constant, function with one or more operators.
– cannot be a subquery.
– cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.
– used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.
– can be used in SELECT list, WHERE clause, ORDER BY clause, etc.
– cannot be the target of an INSERT or UPDATE statement.
Check video on how to create Calculated Columns in a Table:
–> SQL Script used in the above demo:
CREATE TABLE [dbo].[Employee] ( [EmployeeID] int, [EmployeeName] nvarchar(100), [Gender] nchar(1), [DOB] datetime, [DOJ] datetime, [DeptID] int ) GO INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID) VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101) INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID) VALUES ('JHON K', 'M', '1970-03-03', NULL, NULL) INSERT INTO [dbo].[Employee] (EmployeeName, DOB) VALUES ('Brock H', '1980-02-02') GO SELECT * FROM [dbo].[Employee] GO ALTER TABLE [dbo].[Employee] ADD AGE AS (DATEDIFF(YY, DOB, GETDATE())) GO ALTER TABLE [dbo].[Employee] ADD XYZ AS 'nothing' GO SELECT * FROM [dbo].[Employee] GO CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1), Qty smallint, Price money, TotalValue AS (Qty * Price) ) INSERT INTO dbo.Products (Qty, Price) VALUES (5, 200) INSERT INTO dbo.Products (Qty, Price) VALUES (3, 150) select * from dbo.Products GO ALTER TABLE dbo.Products DROP COLUMN TotalValue ALTER TABLE dbo.Products ADD TotalValue AS (Qty * Price) PERSISTED GO -- Final Cleanup DROP TABLE [dbo].[Employee] DROP TABLE dbo.Products GO