Remember that functions – including those found in default constraints – are not executed simultaneously. This can sneak up on you whenever you have multiple function calls in a single statement or multiple default constraints in a single table.
I recently found a flaky unit test that involved datetime columns. And as Martin Fowler says “Few things are more non-deterministic than a call to the system clock.”
But the cause can be subtle. Two columns with the same default of SYSDATETIME can have different values in the same row. To demonstrate, consider this example.
USE tempdb GO -- Create a table CREATE TABLE #Account ( AccountId BIGINT NOT NULL IDENTITY CONSTRAINT PK_Account PRIMARY KEY, AccountDeets NVARCHAR(100), Created DATETIME2 NOT NULL CONSTRAINT DF_Account_Created DEFAULT SYSDATETIME(), LastModified DATETIME2 NOT NULL CONSTRAINT DF_Account_LastModified DEFAULT SYSDATETIME() ) GO |
Now create a procedure that inserts a single row into the table.
-- Create a procedure CREATE PROCEDURE #CreateAccount @AccountDeets NVARCHAR(100) AS INSERT #Account (AccountDeets) VALUES (@AccountDeets); RETURN SELECT SCOPE_IDENTITY(); GO |
Insert rows by executing the procedure several times and look for differences between the two datetime columns.
SET NOCOUNT ON; --Create 10000 rows in #Account declare @i int = 0; while (@i < 10000) begin exec #CreateAccount N'details'; set @i+=1; end select Created, LastModified from #Account where Created <> LastModified; |
This gives something like:
| Created | LastModified |
|---|---|
| 2016-01-18 09:18:15.271 | 2016-01-18 09:18:15.272 |
| 2016-01-18 09:18:15.380 | 2016-01-18 09:18:15.381 |
| 2016-01-18 09:18:15.387 | 2016-01-18 09:18:15.388 |
| 2016-01-18 09:18:15.480 | 2016-01-18 09:18:15.481 |
If I want to depend on these values being exactly the same, I can’t count on the default values. The procedure should look like this:
ALTER PROCEDURE #CreateAccount @AccountDeets NVARCHAR(100) AS DECLARE @Now DATETIME2 = SYSDATETIME(); INSERT #Account (AccountDeets, LastModified, Created) VALUES (@AccountDeets, @Now, @Now); RETURN SELECT SCOPE_IDENTITY(); GO |
