Friday, August 2, 2019

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.


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

SQL Computed Column 
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


SQL Computed Column Output


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.


SQL execution plan computed Column
  
and an execution plan for PERSISTED Computed Column:

SQL execution plan 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.

No comments:

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...