SQL SERVER – Computed Column – PERSISTED and Storage

This is the list of the all the articles in the series of computed column.

SQL SERVER – Computed Column – PERSISTED and Storage

This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance

This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2

This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3

This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2

This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance

This article summarized all the articles related to computed columns.

Every time I go for SQL Server Training or Consultation, I always learn something from my attendees. Sometime I even learn that a simple concept which we think is easy and whole world knows, many times very few knows about the same. In recent training I was surprised to know that many people think that as soon as computed columns are created the column is materialized and the data is now stored in the column just like usual. In fact this is not true. If computed column is not marked as persisted, it is not created when the column is created, in fact it is still computed at run time. Once you mark column as persisted, it is computed right away and stored in the data table.

Let us see quickly following example of the creating computed column.

USE tempdb
GO
-- Create Table
CREATE TABLE UDFEffect (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO UDFEffect (ID,FirstName,LastName)
SELECT TOP 100000 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
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column
ALTER TABLE dbo.UDFEffect ADD
FullName AS (FirstName+' '+LastName)
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Add Computed Column PERSISTED
ALTER TABLE dbo.UDFEffect ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO
-- Check the space used by table
sp_spaceused 'UDFEffect'
GO
-- Clean up Database
DROP TABLE UDFEffect
GO

I have used the system stored procedure sp_spaceused to find out the space used in the query.

From the resultset it is very clear that when I created the computed column, it did not take any additional space in the database. However, when I created computed column marked as PERSISTED it indeed took more space in the data table and the size of the table is grown larger.

I hope this clear it up. In future article I will write performance and efficiency of the computed columns.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Computed Column – PERSISTED and Storage

  1. Hi! I have been following your blog for quite sometime and I really do get useful tips from your posts!

    I am looking forward for you post on the efficiency of computed columns as this has also puzzled me for quite sometime now whether they are efficient or not.

  2. Hello!

    One point I would like to highlight is that once calculated, a PERSISTED computed column is for the purposes of a read operation just like any other column (i.e. uses storage, is not computed at run-time, etc). But, once one of the dependant columns change, the value in the persisted column also changes.

    This is especially important if you are working with a legacy system whose databases have gone through multiple data-cleansing cycles and also have “features” to “reset” the primary keys or other columns. Allow me to explain:

    Legacy systems (i.e. systems running at least since the days of SQL 7.0) typically have evolved over time and have undergone multiple data cleansing operations. Some of these systems might be in the process of being redesigned/re-engineered. However, if you choose to use a persisted computed column for an enhancement you are doing today, think twice.

    One of the features legacy systems tend to have is to allow re-assignment of primary keys. If your computed column is based on any one of the keys, it will be recomputed!

    Therefore, persisted or otherwise, whenever a computed column is used, it needs to be ensured that the source data either never changes, or one actually needs an updated value whenever the source data changes. It should not be used to generate “intelligent” values that are unique or once computed, should never change.

    Pinal:
    Thank you for the wonderful article. I will definitely look forward to your article on the performance & efficiency aspects of the computed columns!

    Have a good day, everyone!

  3. Pingback: SQL SERVER – Computed Column – PERSISTED and Performance Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Computed Column – PERSISTED and Performance – Part 2 Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Computed Column and Performance – Part 3 Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER – Computed Column – PERSISTED and Storage – Part 2 Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER – Computed Columns – Index and Performance Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – Computed Columns – Index and Performance Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

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