SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

SQL Server computed column in database table

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems