SQL SERVER – Capturing INSERT Timestamp in Table

The other day while working on a topic of  Comprehensive Database Performance Health Check recently asked if I know any trick for capturing insert timestamp in the table. Of course, there is a very simple method which one can use to capture the timestamp of the newly inserted rows.

Let us learn it today, how about capturing the timestamp with DEFAULT constrain in SQL Server.

First, let us create a table.

CREATE TABLE TestTable (ID INT, MyDateTime DATETIME DEFAULT CURRENT_TIMESTAMP)
GO

Now let us insert value in the table.

INSERT INTO TestTable (ID)
VALUES (1)

Once the value is inserted, let us select the value from the table.

SELECT *
FROM TestTable
GO

When you retrieve the value from the table, you will notice that it is containing a current timestamp in the table. This is because the column which we had created contained a default constraint.

There is one thing we must remember when we are using this feature and that if anyone inserts the explicit value inside the column TestTable, the column will contain the new value and not the default value. So if you are planning to use this as an audit table, make sure that you do not allow explicit insert into the table. I hope you learn in this blog how to insert timestamp.

I have been building a short video on SQL in the Sixty Seconds. I suggest you subscribe to my channel if you like to learn similar topics.

Reference: Pinal Dave (https://blog.sqlauthority.com)