28 July 2010


Lots of time even simple concepts which we think is easy and whole world knows,many times very few knows about them.One of them is the computed columns in sequel server.Many people thinks that as soon as the computed columns are created,the column is physically come into existence and now the data is stored in that column as usual.In fact its not true;

Essentially there are two kinds of computed columns
1.Virtual column: where the data is not physically stored in the table, values are calculated each time it is referenced in a query
2.Persisted: Once you mark column as persisted, it is computed right away and stored in the data table.and hence it occupies the database space.
(we use the PERSISTED keyword for creating persisted computed columns)
ALTER TABLE dbo.TblEmployee ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED

Another important thing we should keep in mind is we cant create indexes on virtual computed columns,whereas can be in persisted provided computed definition is deterministic.

Example: we wouldn't be permitted to index columns containing a call to the GETDATE() function.

1 comment:

Your comments, Feedbacks and Suggestions are very much valuable to me :)

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...