Getting DATETIME for rows inserted/Modified

am using SQL server 2005 , i have a requirement to get the Creation datetime of all the rows in a particular tabl开发者_运维问答e, unfortunately the table do not have any "rowverion" or datetime column ( i know this is a major design flaw).

so , i was wondering if SQL server maintains datetime for each row inserts.

comments suggestions appreciated

Regards Deepak

No, SQL Server does not timestamp the rows automatically when they are created. As you suggested, for the future, you may want to create a new date_created column and set it default to GETDATE():

ALTER TABLE       your_table  
  ADD CONSTRAINT  dc_your_table_date_created
  DEFAULT         GETDATE()
  FOR             date_created;

You may also use a trigger instead, if you prefer, as @Vash suggested in the other answer.

If this is a bussines business, purpose you should add the column to table and create a triger AFTER INSERT or UPDATE that set the sysdate to those rows. Or you can use the rowversion