PERSISTED Computed Column and Performance Improvement

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.

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.

How to create a computed Column in SQL Server

In this post, we will learn how to create a computed column in SQL and how to define the calculation logic for this computed column.

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs

SQL : PERSISTED Computed Column and Performance Improvement

Here is an example to calculate the DiscountedAmount for each order

DiscountedAmount = OrderAmount * 25%

Syntax: Create SQL Computed Column on Create Table:

The below SQL script creates a table with Computed Column ‘DiscountedAmount’ and field value will be calculated based on other column

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),

    PRIMARY KEY (OrderID),   

);

When you fetch records from table, the Computed field DiscountedAmount will have calculated value based on defined expression (OrderAmount * .25) in schema.

----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

Output:

Syntax: Create SQL Computed Column on Alter Table:

The below SQL script creates a Computed Column ‘DiscountedAmount’ on an existing table

ALTER TABLE dbo.Orders ADD DiscountedAmount AS (OrderAmount * .25)

Syntax: Drop SQL Computed Column:

The below SQL script delete a Computed Column ‘DiscountedAmount’ from table

ALTER TABLE dbo.Orders DROP COLUMN DiscountedAmount