Difference between Persisted and Non Persisted Computed Columns in SQL Server.

Computed Column are derived column based on other existing column in the same table.

There are two types of Computed column namely Persisted and Non-Persisted.

1. Non-Persisted column are calculated on the fly(ie when the select Query executed) whereas Persisted column
are calculated as soon data is stored in the table.

2. Non-Persisted columns do not consume any space as they are calculated only when select the column.
Persisted column consume space.
3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns.

Example:-

CREATE TABLE AA(COL1 INT, COL2 AS COL1*0.20)
INSERT INTO AA (COL1)
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY S1.NAME) FROM SYS.OBJECTS AS S1 CROSS JOIN SYS.OBJECTS AS S2

CREATE TABLE B(COL1 INT, COL2 AS COL1*0.20 PERSISTED)
INSERT INTO B (COL1)
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY S1.NAME) FROM SYS.OBJECTS AS S1 CROSS JOIN SYS.OBJECTS AS S2

RUN THE FOLLOWING CODE TO UNDERSTAND THAT TABLE WITH PERSISTED COMPUTED COLUMNS CONSUMES MORE SPACE WHEN COMPARED TO A TABLE WITH NON-PERSISTED COMPUTED COLUMNS. REFER THE COLUMN NAMED DATA

EXEC SP_SPACEUSED AA
EXEC SP_SPACEUSED B

Advertisements

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