COMPUTED COLUMNS IN SQL SERVER

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)

Example:

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.