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.

SQL SERVER - Computed Column - PERSISTED and Storage persisted

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

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – FIX: ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char
Next Post
SQLAuthority News – 2 Sessions at TechInsight 2010 – June 29 – July 1, 2010

Related Posts

7 Comments. Leave new

  • Thank you! It was useful

    Reply
  • 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.

    Reply
  • Nakul Vachhrajani
    July 29, 2010 10:21 pm

    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!

    Reply
    • This is just one good reason to use surrogate keys. It’s hard to imagine why you would do computations on surrogate keys.

      Reply
  • it is not working for me. i tried the same example. am i missing something? i am using SQL Server 2005.
    Thanks.

    Reply
  • I am not able to open the links of your previous topics. Could you please suggest and help.

    Reply

Leave a Reply

Menu