This is the list of the all the articles in the series of computed column.
This article talks about how computed columns are created and why they take more storage space than before.
This article talks about how PERSISTED columns give better performance than non-persisted columns.
This article talks about how non-persisted columns give better performance than PERSISTED columns.
This article talks about how Index improves the performance of Computed Columns.
This article talks about how creating index on computed column does not grow the row length of table.
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)