In this post, we will learn how to create a PERSISTED computed column in SQL and how does it improve the performance of computed Column in large data calculation.
Persisted Computed Column means the SQL Server physically stores the data of computed columns and when the data is changed in table, the SQL server computes the value for computed column based on the defined calculation expression in schema.
So when you fetch data from table, it doesn’t perform any calculation and simply retrieves the value from table for computed column.
Syntax: Create SQL PERSISTED Computed Column on Create Table:
The below SQL script creates a table with PERSISTED Computed Column ‘DiscountedAmount’.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
StatusID int,
OrderAmount Decimal(10,2),
OrderDate date DEFAULT GETDATE(),
DiscountedAmount AS (OrderAmount * .25) PERSISTED,
PRIMARY KEY (OrderID),
);
When you update the data in table, the value of Computed field DiscountedAmount will be will be updated by SQL server.
----Inserts value in the table
Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)
Select 1, 100, 1, 100
Insert dbo.Orders(OrderID,OrderNumber,StatusID,OrderAmount)
Select 2, 101, 1, 300
----Display the rows in the table
Select * from dbo.Orders
Syntax: Create SQL PERSISTED Computed Column on Alter Table:
The below SQL script creates a PERSISTED Computed Column ‘DiscountedAmount’ on an existing table
ALTER TABLE dbo.Orders ADD DiscountedAmount AS (OrderAmount * .25) PERSISTED
Syntax: Drop SQL PERSISTED Computed Column:
The below SQL script delete a PERSISTED Computed Column ‘DiscountedAmount’ from table
ALTER TABLE dbo.Orders DROP COLUMN DiscountedAmount
Performance Improvement by using PERSISTED Computed Column instead of Computed Column:
Here is an execution plan, which show that there two scalar operator for computed column without Persisted tag.
and an execution plan for PERSISTED Computed Column:
Conclusion that If the computed column is PERSISTED, there is no performance issue in selecting the data but there will be extra overhead while updating or inserting new row.
