There’s always going to be reasons to have computed columns, the real choice is down to how you want to interact with that column! Below is break down of the differences between a computed column and persisted computed.

You can only create indexes on PERSISTED columns

Storage Comparison

Using a computed column creates a virtual column, if you use PERSISTED it adds the column to the physical files. below is example of how you see the extra storage used by having a persisted column

/*
Storage with COMPUTED vs. PERSISTED COMPUTED
*/

-- Create table to work from (Copy of the PERSONS table)
SELECT * INTO person.personsComputed
FROM person.persons

-- Create temp table to put sp_spaceused results in
CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
-- insert 1st set of results (Table no computed columns)
insert #tmpTableSizes
EXEC sp_spaceused 'person.personsComputed'
-- ADD COMPUTED column
ALTER TABLE person.personscomputed ADD fullname AS firstname + ' ' + LastName
-- insert 2nd set of results (Table with computed column)
insert #tmpTableSizes
EXEC sp_spaceused 'person.personsComputed'

-- ADD a PERSISTANT COMPUTED column
ALTER TABLE person.personsComputed ADD fullname2 AS firstname + ' ' + LastName PERSISTED

-- insert 3rd set of results (Table a computed column AND PERSISTANT COMPUTED column)
insert #tmpTableSizes
EXEC sp_spaceused 'person.personsComputed'
-- Show Results (Note 1 and 2 are the same, 3 is larger)
SELECT * FROM #tmpTableSizes
-- Note the final results larger data size due to the added persisted column

Execution Plans

As computed columns are virtual, they impact the performance of queries using them, below is quick example of how having the physical persisted column gives performance benefits by having a simpler execution plan and a lower cost to the query.

/*
Execution Plans with COMPUTED vs PERSISTED COMPUTED
*/

USE AdventureWorks2012
GO

-- Enable INCLUDE EXECUTION PLANS (Ctrl+M)

-- Create table to work from (Copy of the PERSONS table)
SELECT * INTO person.personsComputedExecution
FROM person.person

-- ADD COMPUTED column
ALTER TABLE person.personsComputedExecution ADD fullname AS firstname + ' ' + LastName

-- Run select statement and few Execution Plan
SELECT firstname, lastname, fullname
from person.personsComputedExecution
WHERE emailpromotion =2

ep1

Cost: 2.86159

Extra Compute Scalar to create the virtual columns.

-- REMOVE COMPUTED COLUMN
ALTER TABLE person.personsComputedExecution DROP COLUMN FullName
-- ADD PERSISTED COMPUTED column
ALTER TABLE person.personsComputedExecution ADD fullname AS firstname + ' ' + LastName PERSISTED
-- Run select statement and few Execution Plan
SELECT firstname, lastname, fullname
from person.personsComputedExecution
WHERE emailpromotion =2

ep2

Cost: 2.85959 (Lower execution cost compared to a virtual computed column)

Removes the need compute scalar.

Resources and Links

Creating Indexes on Computed Columns

http://technet.microsoft.com/en-us/library/ms189292(v=sql.105).aspx

Pinal Dave has a collection of posts on computed columns

http://blog.sqlauthority.com/2010/08/03/sql-server-computed-column-persisted-and%C2%A0performance/

Advertisements