SQL - PERSISTED Computed Column and Performance Improvement

In this post, we will learn how to create a PERSISTED computed column in SQL Server and how does it improve the performance of sql computed Column in large data calculation and will analyze the SQL performance by using sql server database perforamce monitoring tools. 

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.

SQL : How to create a computed Column in SQL Server

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.