Brian Brian - 5 months ago 17
SQL Question

Auto-computed column in SQL Server 2012

Is it possible for a column in to be auto-calculated by SQL Server 2012 itself?

For example: I have three columns

START_DATE
,
END_DATE
and
DURATION
.

I want to get the duration by doing this :

DURATION = END_DATE - START_DATE


So I get the duration in number of days.

Is it possible for SQL Server 2012 to do it automatically on record creation ?

Answer

Yes, sure - just define Duration to be a computed column:

ALTER TABLE dbo.YourTable
ADD Duration AS DATEDIFF(DAY, START_DATE, END_DATE) PERSISTED

and off you go. Now Duration will always show the difference (in days) between these two other columns. The value computed will be stored along side your other column values, if you use the PERSISTED keyword. This column will be updated if any of the two "dependent" columns changes, too.

Update: if you want to get the difference between a date and today, you can use

ALTER TABLE dbo.YourTable
ADD Duration AS DATEDIFF(DAY, START_DATE, GETDATE()) 

but unfortunately, since the GETDATE() function is non-deterministic (after all - it's return value changes every time you call it), you cannot use the PERSISTED keyword. This means: every time you access the column (ask for its value), the calculation will be done again.