Alex A
asked on
SQL Server: table row insert/update datetime
Hi, how can I find table row insert and last update datetime?
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Russell Fox
If you don't have a specific column to track that data, you'll need to go looking through the log files to find it. I always have DateCreated (default = GETDATE()) and DateUpdated DATETIME columns in all relevant tables. Data is only updated through our stored procedures, so all updated procs do a "SET DateUpdated = GETDATE()". If you don't control your inputs with stored procedures or other code, you can also put in an ON UPDATE trigger to update that column whenever a record is changed, and that will fire even if you change bulk records through a query in Management Studio.
But be aware that you're widening the rows, and thus affecting overall performance of the table, by adding these columns.
Instead, if possible, I suggest using a trigger and storing that data in a separate table. But most people don't do that. Still, if you have a (very) large table with, say, 100M rows, even an additional 16 bytes per row is 1.6B bytes more in the table. Moreover, many people also store a character id of who added and who changed, widening the row that much more.
Instead, if possible, I suggest using a trigger and storing that data in a separate table. But most people don't do that. Still, if you have a (very) large table with, say, 100M rows, even an additional 16 bytes per row is 1.6B bytes more in the table. Moreover, many people also store a character id of who added and who changed, widening the row that much more.