How do I create column calculated from another column?

Question

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

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

Also its values should increment as Age increases.

Only when you select the values of the table or rather when you access anyone would know if the age is upto date. So you can use a trigger/stored proecedure to do that.. isn't it? [something on similar lines](http://stackoverflow.com/questions/12329149/calculating-age-from-birthday-with-oracle-plsql-trigger-and-insert-the-age-in-ta) — bonCodigo, Nov 29 '12 at 13:02
I would simply create a VIEW on top of the table with the last column of the view definition as `datediff(yy, DOB, getUTCDate())`. — Vikdor, Nov 29 '12 at 13:03
Create a view that calculates the age. There is no need to store this kind of information. — , Nov 29 '12 at 13:03

SchmitzIT · Accepted Answer · 2012-11-29T13:21:41.513

You should use a computed column to solve this problem. Something with a definition similar to this:

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

Original statement taken from and further information available at BlackWasp.

Edit:

MSDN explains computed columns as:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The 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 are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly 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 select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

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

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

A computed column cannot be the target of an INSERT or UPDATE statement.

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

Source: MSDN - Computed Columns

I suggest you add some info here, too, not just the link. Like what is a computed column, can this be peristed, can it be indexed, etc. — ypercubeᵀᴹ, Nov 29 '12 at 13:10
very nice description, but it really doesn't find the age. It finds the difference in whole years between 2 dates. DOB = '2012-12-31', getdate() = '2013-01-01' = 1 year — t-clausen.dk, Nov 29 '12 at 13:38

score 4 · Answer 2 · edited Jul 07 '15 at 05:47

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

score 2 · Answer 3 · answered Jul 07 '15 at 04:58

2

Create Table with auto-generated column,

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

answered Jul 07 '15 at 04:58

kasim

346
2
5
23

score 1 · Answer 4 · answered Nov 29 '12 at 13:34

1

This is the correct way of getting the 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

answered Nov 29 '12 at 13:34

t-clausen.dk

43,517
12
59
92

How do I create column calculated from another column?

4 Answers4

Linked