Computed columns - Persisted Vs Non-persisted‏

Computed columns are derived columns based on other existing columns in the same table. Refer to this post http://www.sqlservercurry.com/2012/11/computed-columns-in-sql-server.html for more information:

There are two types of computed columns namely persisted and non-persisted.

There are some major differences between these two

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns

Consider the following set of code



create table #t1(col1 int, col2 as col1*0.20)
insert into #t1 (col1)
select top 5000 row_number() over (order by s1.name) from sys.objects as s1 cross join sys.objects as s2

create table #t2(col1 int, col2 as col1*0.20 persisted)
insert into #t2 (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 tempdb..spaceused #t1
exec tempdb..spaceused #t2

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan