Computed column with Persisted value, Performance effect

In previous blog http://shamas-saeed.blogspot.com/2011/05/creating-computed-calculated-column-in.html

we have learned how to create a computed or calculated column and what is its limitation. In this blog we will what is difference between computed columns and persisted computed column and why computed column persisted is required.

Persisted computed columns are giving better performance than the cost of reading IO from database. Although you can see by creating index it will increase performance but our focus is to show performance difference

·         Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

·         A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns Value1 and Value2, the computed column Value1 + Value2 can be indexed, but computed column Value1 + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

/* Code sample

USE AdventureWorks2008R2_Data

GO

-- Create Table

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[nonpresisted]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[nonpresisted]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[presisted]') AND TYPE IN (N'U'))

DROP TABLE [dbo].[presisted]

GO

CREATE TABLE nonpresisted (ID INT,

FirstName VARCHAR(100),

LastName CHAR(8000))

GO

CREATE TABLE presisted (ID INT,

FirstName VARCHAR(100),

LastName CHAR(8000))

GO

-- Insert One Hundred Thousand Records

INSERT INTO nonpresisted (ID,FirstName,LastName)

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Bob',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'

ELSE 'Brown' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

INSERT INTO presisted (ID,FirstName,LastName)

SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Bob',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'

ELSE 'Brown' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

-- Add Computed Column

ALTER TABLE dbo.nonpresisted ADD

FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)

GO

-- Add Computed Column PERSISTED

ALTER TABLE dbo.presisted ADD

FullName_P AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12) PERSISTED

GO

 -- Select Comparision

SELECT FullName

FROM dbo.nonpresisted

WHERE FullName = 531441

GO

SELECT FullName_P

FROM dbo.presisted

WHERE FullName_P = 531441

GO

 --Clean up Database

DROP TABLE nonpresisted

DROP TABLE presisted

GO

*/

As shown from attached screen we can see how much computed column index will improve performance for example by creating index on FullName it will improve 99.7884 percent. I will post another blog for creating and effect of index on computed column.