26

I have a SQL Server 2008 database. This database has a Table called "Book". "Book" has the following properties:

  • ID (int)
  • Title (nvarchar(256))
  • PublishDate (datetime)

I need to create a computed column called "AgeInMinutes". I'm not very familiar with computed columns. I understand the concept, but I'm not sure how to do it. In SQL Server Management studio, in the "Column Properties" area, I see a property called "(Formula)" in the Table Designer section. I assume I need to enter my calculation here. However, I'm not sure what to put here. Can somebody please help me?

Thank you!

Phone Developer
  • 1,411
  • 4
  • 25
  • 36
  • 1
    What are you planning on using this computed column for? Don't use it in a `WHERE` clause as you will end up with a full scan. – Martin Smith Nov 07 '11 at 15:53

2 Answers2

43

You can define the column in your CREATE TABLE as:

AgeInMinutes as (DATEDIFF(minute, PublishDate, GETDATE())

Alternatively, just do

ALTER TABLE Book
ADD AgeInMinutes as (DATEDIFF(minute, PublishDate, GETDATE())
JNK
  • 63,321
  • 15
  • 122
  • 138
  • 4
    I think it's worth making it extra clear that in the CREATE TABLE definition, you don't specify a data type. I overlooked that at first. – hurleystylee Sep 17 '15 at 20:44
  • 1
    You can also specify that the calculation is done and then stored by using the PERSISTED keyword eg AgeInMinutes as (DATEDIFF(minute, PublishDate, GETDATE()) PERSISTED – Lord Darth Vader Jun 05 '18 at 07:33
0

Modify computed column through SSMS. Right click on table and select Design then click on computed column then see in column properties there is one option like. Computed column specification there you can alter COMPUTED COLUMN.

Brad Larson
  • 170,088
  • 45
  • 397
  • 571
Santosh S.Pawar
  • 27