Computed Column Sample in SQL Server Database Table
A computed column in SQL Server is an expression field which uses other columns in the table as input in the expression. For example, a Total computed column in SQL database table can sum two or more amount fields in the table and display as output. Using this way, instead of manually summing all values the SQL engine will do this for application developers. Or instead of manually updating Total field, the computed column expression will always display the latest result for the developer.
In this T-SQL tutorial, I will display computed column in SQL Server 2008 R2 database but SQL programmers can also work with computed columns in SQL Server 2005 or in other versions.
Create Table ComputedColumnAge (
Id int identity(1,1),
InsertDate datetime,
Age AS (
CASE
WHEN DATEDIFF(mi, InsertDate, GETDATE()) = 0 Then 'Just inserted'
WHEN DATEDIFF(mi, InsertDate, GETDATE()) = 1 Then '1 minute old'
WHEN (DATEDIFF(mi, InsertDate, GETDATE()) BETWEEN 2 AND 5) Then 'Within 5 minutes'
WHEN (DATEDIFF(mi, InsertDate, GETDATE()) BETWEEN 6 AND 10) Then '5-10 minutes'
WHEN (DATEDIFF(mi, InsertDate, GETDATE()) > 10 ) Then 'Older than 10 minutes'
END
) -- computed column
)
GO;
insert into ComputedColumnAge select GETDATE()
insert into ComputedColumnAge select dateadd(mi,-1,GETDATE())
select * from ComputedColumnAge
If you repeat the INSERT statement with a few minutes period and execute a SELECT statement over the computed column sample table, you will see that the Age SQL computed column will be each time showing a different value.
For example, if you execute SQL Select statement after 10 minutes, all rows will have "Older than 10 minutes" in SQL Server computed column Age.
The use of SQL computed column in a database table definition, prevents application developers and database administrators to update the target rows using a scheduled job, etc. The computed column is showing each time the value which is calculated using the formula stated in the definition of the table computed column.