Monthly Archives: January 2015

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