Home > DB Concepts, SQL Basics > SQL Basics – Persisted and Non-Persisted Computed columns in SQL Server

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

2. Constant

3. Function

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:

Computed Columns
 

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

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: