[T-SQL] Computed Column – PERSISTED and Storage

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.