Logical vs Physical Computed Columns

Many people have asked me about the difference between using and not using ‘PERSISTED’ in a computed column; and how do we know whether the column values are being stored logically or physically. 

I would like to share the answer to these questions and some more information here.

Let me start the with the definitions first.

  • A computed column is basically a virtual column that is not physically stored in the table.
  • A computed column which is marked as PERSISTED will have its data stored physically.

Let’s see the difference between computed column and persisted computed column:

CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable smallint ,
UnitPrice money ,
InventoryValue AS QtyAvailable * UnitPrice );

If we expand the table in object explorer, we see the column marked as computed with the data type assigned based on the data of the columns & that also defines the computed column.

ComputedColumnIcon

If we verify the properties of the table, it shows the following information.

Computed Column

Also, if we look into the system tables, it appears as a computed column.

syscomputedcolumnscomparison

Now drop this table and recreate it mentioning the keyword “PERSISTED” at the end of the table definition. 

DROP TABLE dbo.Products
Go 

CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable smallint ,
UnitPrice money ,
InventoryValue AS QtyAvailable * UnitPrice  PERSISTED) 

Now, look at the properties, values or attributes as shown in the Picture-1, Picture-2 and Picture-3 along with the values of “sys.columns” system table.

You will notice that there is no difference except for “is_persisted” column value in “sys.computed_columns” system table. And if we talk about identifying whether it’s storing the data physically or logically, please continue reading the article. 

CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable smallint ,
UnitPrice money ,
InventoryValue AS QtyAvailable * UnitPrice );  

–INSERTING 1000 ROWS
DECLARE
@QtyAvailable SMALLINT,
@UnitPrice MONEY

SELECT @QtyAvailable = 1
SELECT @UnitPrice = 10

while @QtyAvailable >=1 and @QtyAvailable <= 1000
BEGIN
    INSERT INTO dbo.Products values(@QtyAvailable, @UnitPrice)
    SELECT @QtyAvailable = @QtyAvailable + 1
    SELECT @UnitPrice = @UnitPrice + 1
END
GO
sp_spaceused ‘dbo.Products’
GO

SpaceUsedAfterInsert_CC

f you see, the data space it consumed is 24 KB out of the reserved 72 KB. Unused space remains 40 KB after the index size is being deducted.

Let us check the same for computed column with PERSISTED.

CREATE TABLE dbo.Products (
ProductID int IDENTITY (1,1) NOT NULL ,
QtyAvailable smallint ,
UnitPrice money ,
InventoryValue AS QtyAvailable * UnitPrice  PERSISTED) 

–INSERTING 1000 ROWS

DECLARE
@QtyAvailable SMALLINT,
@UnitPrice MONEY

SELECT @QtyAvailable = 1
SELECT @UnitPrice = 10

while @QtyAvailable >=1 and @QtyAvailable <= 1000
BEGIN
    INSERT INTO dbo.Products values(@QtyAvailable, @UnitPrice)
    SELECT @QtyAvailable = @QtyAvailable + 1
    SELECT @UnitPrice = @UnitPrice + 1
END
GO
sp_spaceused ‘dbo.Products’
GO

After using “PERSISTED”, the computed column values are being stored physically in the disk as you can see the difference in the space consumption.

SpaceUsedAfterInsert_PCC

Hope you liked this article & found it informative. Please do not hesitate to ask me for any further clarification.

One comment

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 )

Connecting to %s