How do I set a value for existing rows when creating a new timestamp column?

Question

I have the following table:

Study   id
Pepsi   1
Coke    2
Sprite  3

I need to add a new column timestamp in the above table. i.e, study creation time and date will be stored in this column. What value should I have set for existing rows? Or should the "Timestamp" column have a value only for newly created rows?

I have used the following query to add the new column:

alter table Study add Timestamp datetime

Answer 1

There is no way to tell you what value you should set for existing rows - that is up to you to decide. If you can somehow retrieve the creation time by piecing together other information, then perhaps you can do this one by one, or you could just leave the existing rows to NULL.

Setting a default like GETDATE() for the column, and setting it to NOT NULL, forces all of the existing rows to inherit the current date and time - and you won't be able to set those back to NULL. I'm quite opposed to using garbage token values like 1900-01-01 to represent unknown, and I also don't believe in modifying the code to say something like "if the date is October 8, 2013 then that's because we just didn't know." So I would suggest adding a NULLable column with a default:

ALTER TABLE dbo.Study ADD CreationTime DATETIME NULL DEFAULT CURRENT_TIMESTAMP;
GO

Note that if you leave the column nullable, then the DEFAULT constraint is only useful if DML never sets it to NULL. If an INSERT statement, for example, explicitly places NULL there, the default is ignored. A way around this is to use a trigger (just like you would handle an update):

CREATE TRIGGER dbo.StudyCreationTime
ON dbo.Study
FOR INSERT 
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE s
    SET s.CreationTime = CURRENT_TIMESTAMP
    FROM dbo.Study AS s
    INNER JOIN inserted AS i
    ON s.StudyID = i.StudyID;
END
GO