SQL Server 2005 and row versioning

Yes, this is the path that I would adopt even for auditing issues (I would also use a field to identify the user who made the change, but this is not the case). For SQL Server 2005 you can use a default GETDATE() or ( CURRENT_TIMESTAMP ) value for the insert date and a trigger for the update date. For SQL Server 2008+ you can use the SYSDATETIME() function and the most accurate type DATETIME2 .

CREATE TABLE dbo.MinhaTabela
(  
   ID INT IDENTITY(1,1) PRIMARY KEY,
   -- suas colunas
   DataDeInclusao  DATETIME NOT NULL
       CONSTRAINT DF_MinhaTabela_Inclusao DEFAULT (GETDATE()),
   DataDeAtualizacao DATETIME NULL -- Ou com DEFAULT de acordo com seus requisitos
);

CREATE TRIGGER dbo.TRG_MinhaTabelaAtualizada
ON dbo.MinhaTabela
AFTER UPDATE 
AS
    UPDATE dbo.MinhaTabela
    SET DataDeAtualizacao = GETDATE()
    FROM Inserted i
    WHERE i.ID = dbo.MinhaTabela.ID;

See that to meet your requirement only one column DataUltimaAtualizacao (with the constraint default and trigger ) would suffice, but it's always good to differentiate update insert .

Finally, do not forget to index the two dates (to optimize your queries).

CREATE INDEX IDX_MinhaTabelaInclusao ON  dbo.MinhaTabela(DataDeInclusao);
CREATE INDEX IDX_MinhaTabelaAtualizacao ON  dbo.MinhaTabela(DataDeAtualizacao);

Example in SQL Fiddle

Fonts :

• SOE - created and modified timestamp in sql server 2008
• SOE - How do I add a "last updated" column in a SQL Server 2008 R2 table?

In this answer , I teach a command to create a trigger for each table in your database. This can be useful if you plan to audit all tables.

timestamp and rowversion are not suitable for tracking the last change date because they do not save a date as such, but a binary number that changes every time the row changes. The purpose of this type of data is simply to resolve competition conflicts for large, widely accessed systems at reading and writing levels.