A default can insert timestamp values when the caller omits a datetime column. I use a session-local CURRENT_TIMESTAMP example.
CREATE TABLE #TestTable
(ID int, MyDateTime datetime NULL DEFAULT CURRENT_TIMESTAMP);
INSERT #TestTable (ID) VALUES (1);
INSERT #TestTable (ID, MyDateTime) VALUES (2,'20000101'),(3,NULL);
SELECT ID, MyDateTime FROM #TestTable ORDER BY ID;
DROP TABLE #TestTable;
Row 1 receives current server-local datetime. Row 2 retains its explicit historical value. Row 3 remains NULL because the column permits it. Defaults apply to omitted columns or DEFAULT, rather than explicitly supplied NULL.
CURRENT_TIMESTAMP returns datetime. SQL Server timestamp instead names the deprecated synonym for rowversion, a binary version marker. For greater precision and UTC, consider datetime2 with SYSUTCDATETIME().
This default does not update itself when a row changes. Define who can insert or modify its value. Assess stronger auditing requirements separately.
Related reading
An insertion-time default is not a tamper-proof audit trail, it is a value supplied under defined insert conditions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
1 Comment. Leave new
CREATE TABLE TestTable (ID INT, MyDateTime DATETIME DEFAULT GETDATE())
GO
The above should also give the same result, what do you recommend GETDATE function or CURRENT_TIMESTAMP?