How to create a column calculated from another column?

Question

I need to create an age column in a SQL Server database.

The values ​​of this column should be calculated based on the values ​​of the DOB column.

Also, its values ​​should increase with age .

Answer 1

To solve this problem, you must use a computed column. Something with a definition like this:

 ALTER TABLE Customers ADD Age AS datediff(year, DOB ,getdate()) 

Original expression taken from and additional information available at BlackWasp .

Edit:

MSDN explains computed columns as:

A calculated column is calculated from an expression that can use other columns in the same table. An expression can be an incomplete column name, constant, function, and any combination of them, related one or more operators. An expression cannot be a subquery.

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values ​​are recalculated every time they refer to the request. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in a table. Their values ​​are updated when any columns that are part of their calculation change. From marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not accurate. Additionally, if the computed column refers to a CLR function, the Database Engine cannot check if the function is actually deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it. For more information, see Creating Indexes on Computed Columns.

Computed columns can be used in selected lists, WHERE, ORDER BY clauses, or any other locations in which regular expressions may be followed by the following exceptions:

Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked Stored. A calculated column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE if the value of the calculated column is determined by a deterministic expression and the result data type is allowed in the index columns.

For example, if a table has integer columns a and b, the calculated column a + b may be indexed, but the calculated column a + DATEPART (dd, GETDATE ()) cannot be indexed, since the value may change> in subsequent calls.

The calculated column cannot be the object of an INSERT or UPDATE statement.

The database engine automatically determines the invalidity of calculated columns based on the expressions used. The result of most expressions is considered null, even if only immutable columns are present, because possible flows or overflows will be null results. Use the COLUMNPROPERTY function using the AllowsNull property that examines the nullability value of any computed column in the table. An expression that is null can be made immutable by specifying ISNULL (check_expression, constant), where the constant is a non-null value, replaced by any null result.

Source: MSDN - Computed Columns

Answer 2

Code snippet

 ALTER TABLE TheTable ADD DOB AS CASE WHEN MONTH(Birth) > MONTH(ISNULL(Death, SYSDATETIME())) OR ( MONTH(Birth) = MONTH(ISNULL(Death, SYSDATETIME())) AND DAY(Birth) >= DAY(ISNULL(Death, SYSDATETIME())) ) THEN DATEDIFF(YEAR, Birth, ISNULL(Death, SYSDATETIME())) - 1 ELSE DATEDIFF(YEAR, Birth, ISNULL(Death, SYSDATETIME())) END 

Answer 3

This is the right way to get age:

 alter table <yourtable> add age as datediff(year, DOB, getdate())- case when month(DOB)*32 + day(DOB) > month(getdate()) * 32 + day(getdate()) then 1 else 0 end 

Answer 4

Create a table with an automatically created column,

 CREATE TABLE Person2 (Id int IDENTITY(1,1) NOT NULL, Name nvarchar(50), DOB date, Age AS DATEDIFF(YEAR, DOB ,GETDATE()) )