SQL Server 2008 Row Insert and Update timestamps

Question

I need to add two columns to a database table in SQL Server 2008 R2:

• createTS - date and time when row is inserted
• updateTS - date and time when row is updated

I have a few questions:

1. What column data type should I employ for each of these?
2. createTS needs to be set only once, when the row is inserted. When I tried the datetime type for this column and added a Default Value or Binding of getdate(), the column value was appropriately set. Is this the best way to fulfill the purpose of this column? I considered timestamp data type, but that is, in my opinion, nearly a misnomer!
3. updateTS needs to be set to the date and time of the moment when the row is updated. In SQL Server, there is no ON UPDATE CURRENT_TIMESTAMP (as in MySQL), so it looks like I have to resort to using a trigger. Is this right and how would I go about doing that?

So there is a starting point for anyone who would like to answer this question, here is the create table script:

CREATE TABLE [dbo].[names]
(
    [name] [nvarchar](64) NOT NULL,
    [createTS] [datetime] NOT NULL CONSTRAINT [DF_names_createTS]  DEFAULT (getdate()),
    [updateTS] [datetime] NOT NULL,
    CONSTRAINT [PK_names] PRIMARY KEY CLUSTERED 
    (
        [name] ASC
    )
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

try

CREATE TABLE [dbo].[Names] (     [Name] [nvarchar](64) NOT NULL,     [CreateTS] [smalldatetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,     [UpdateTS] [smalldatetime] NOT NULL  ) 

PS I think a smalldatetime is good enough. You may decide differently.

Can you not do this at the "moment of impact" ?

In Sql Server, this is common:

Update dbo.MyTable  Set   ColA = @SomeValue ,  UpdateDS = CURRENT_TIMESTAMP Where........... 

Sql Server has a "timestamp" datatype.

But it may not be what you think.

Here is a reference:

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

Here is a little RowVersion (synonym for timestamp) example:

CREATE TABLE [dbo].[Names] (     [Name] [nvarchar](64) NOT NULL,     RowVers rowversion ,     [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,     [UpdateTS] [datetime] NOT NULL  )   INSERT INTO dbo.Names (Name,UpdateTS) select 'John' , CURRENT_TIMESTAMP UNION ALL select 'Mary' , CURRENT_TIMESTAMP UNION ALL select 'Paul' , CURRENT_TIMESTAMP  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]  Update dbo.Names Set Name = Name  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names] 

Maybe a complete working example:

DROP TABLE [dbo].[Names] GO   CREATE TABLE [dbo].[Names] (     [Name] [nvarchar](64) NOT NULL,     RowVers rowversion ,     [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,     [UpdateTS] [datetime] NOT NULL  )  GO  CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names AFTER INSERT, UPDATE AS  BEGIN  Update dbo.Names Set UpdateTS = CURRENT_TIMESTAMP from dbo.Names myAlias , inserted triggerInsertedTable where  triggerInsertedTable.Name = myAlias.Name  END   GO       INSERT INTO dbo.Names (Name,UpdateTS) select 'John' , CURRENT_TIMESTAMP UNION ALL select 'Mary' , CURRENT_TIMESTAMP UNION ALL select 'Paul' , CURRENT_TIMESTAMP  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]  Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names] 

Matching on the "Name" value is probably not wise.

Try this more mainstream example with a SurrogateKey

DROP TABLE [dbo].[Names] GO   CREATE TABLE [dbo].[Names] (     SurrogateKey int not null Primary Key Identity (1001,1),     [Name] [nvarchar](64) NOT NULL,     RowVers rowversion ,     [CreateTS] [datetime] NOT NULL CONSTRAINT CreateTS_DF DEFAULT CURRENT_TIMESTAMP,     [UpdateTS] [datetime] NOT NULL  )  GO  CREATE TRIGGER dbo.trgKeepUpdateDateInSync_ByeByeBye ON dbo.Names AFTER UPDATE AS  BEGIN     UPDATE dbo.Names     SET UpdateTS = CURRENT_TIMESTAMP     From  dbo.Names myAlias     WHERE exists ( select null from inserted triggerInsertedTable where myAlias.SurrogateKey = triggerInsertedTable.SurrogateKey)  END   GO       INSERT INTO dbo.Names (Name,UpdateTS) select 'John' , CURRENT_TIMESTAMP UNION ALL select 'Mary' , CURRENT_TIMESTAMP UNION ALL select 'Paul' , CURRENT_TIMESTAMP  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names]  Update dbo.Names Set Name = Name , UpdateTS = '03/03/2003' /* notice that even though I set it to 2003, the trigger takes over */  select *  ,  ConvertedRowVers = CONVERT(bigint,RowVers) from [dbo].[Names] 

Answer 2

As an alternative to using a trigger, you might like to consider creating a stored procedure to handle the INSERTs that takes most of the columns as arguments and gets the CURRENT_TIMESTAMP which it includes in the final INSERT to the database. You could do the same for the CREATE. You may also be able to set things up so that users cannot execute INSERT and CREATE statements other than via the stored procedures.

I have to admit that I haven't actually done this myself so I'm not at all sure of the details.