Archive for October 2, 2009

SQL Basics – Persisted and Non-Persisted Computed columns in SQL Server

October 2, 2009 Leave a comment

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

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

SELECT * FROM [dbo].[Employee]

ALTER TABLE [dbo].[Employee]

ALTER TABLE [dbo].[Employee]
ADD XYZ AS 'nothing'

SELECT * FROM [dbo].[Employee]

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

ALTER TABLE dbo.Products

ALTER TABLE dbo.Products
ADD TotalValue AS (Qty * Price) PERSISTED

-- Final Cleanup
DROP TABLE [dbo].[Employee]
DROP TABLE dbo.Products