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.
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.
/* Performance Effect */
/* 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
