Adding a Default Current Date/Time Column to SQL Server Tables

Tracking when records are created in your database is often crucial for auditing, data analysis, and application logic. SQL Server provides a straightforward method to automatically capture this information by setting a default value to the current date and time for a column. This article will guide you through using the ALTER TABLE ADD COLUMN DEFAULT GETDATE() command to achieve this, ensuring every new row is timestamped upon insertion.

Let’s illustrate this with a practical example. We’ll start by creating a simple table without a date-time column, and then enhance it to automatically record the insertion time for new entries.

First, we create a basic table named TestTable with an ID and Col1 column. We then insert a couple of records to populate it.

USE tempdb
GO

-- Create Table
CREATE TABLE TestTable (
    ID INT,
    Col1 VARCHAR(100)
);

-- Insert Values
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'First' UNION ALL
SELECT 2, 'Second';

-- Select from table
SELECT * FROM TestTable
GO

Now, let’s add a new column named DateInserted to our TestTable. We will use the ALTER TABLE ADD COLUMN statement along with the DEFAULT GETDATE() constraint. This will automatically populate the DateInserted column with the current date and time whenever a new row is inserted into the table, and no value is explicitly provided for this column.

-- Add Column with Default Current Date Time
ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());

-- Select from table
SELECT * FROM TestTable
GO

It’s important to note that when you add a column with a DEFAULT GETDATE() constraint to an existing table, SQL Server will populate this new column with the current date and time for all existing rows as well. This is a key behavior to understand. If you had historical data in your table and needed to preserve a different creation timestamp, this method alone might not suffice. However, for tracking the point from which you start recording insertion times going forward, it’s perfectly effective.

To see this in action for new records, let’s insert a couple of new rows into TestTable without specifying a value for the DateInserted column.

-- Now Insert New Rows
INSERT INTO TestTable (ID, Col1)
SELECT 3, 'Third';

INSERT INTO TestTable (ID, Col1)
SELECT 4, 'Fourth';
GO

-- Select from table
SELECT * FROM TestTable
GO

When you query the table again, you will observe that the DateInserted column for the newly inserted rows is automatically filled with the current date and time at the moment of insertion. This confirms that the DEFAULT GETDATE() setting is working as expected for new records.

Finally, let’s clean up the temporary table we created.

-- Clean up
DROP TABLE TestTable
GO

In summary, using ALTER TABLE ADD COLUMN DEFAULT GETDATE() SQL Server is a simple and efficient way to automatically timestamp new records in your SQL Server tables. While it applies the current timestamp to existing rows upon column addition, it reliably captures the insertion time for all subsequent new entries.

This leads to an interesting follow-up question: Is there a built-in way to automatically track when a row was updated in SQL Server, without explicitly updating a dedicated datetime column in your update statements?

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