Search code examples
sqldateconstraintssql-server-2016temporal-tables

Temporal table default constraint for ValidFrom does not work

I have this specific problem. I am running Microsoft SQL Server Management Studio (13.0.16000.28) and I want to create temporal table Price. I want to set 'ValidFrom' column to default Value for example '1900-01-01 00:00:00'. Code I am running is 

CREATE TABLE [DWH_STORE].[dbo].[Price] (
  ID int,
  ProdName nvarchar(40),
  Price int,
  ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START CONSTRAINT DF_ValidFrom DEFAULT '1900-01-01 00:00:00' NOT NULL,
  ValidTo datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL,
          PERIOD FOR SYSTEM_TIME ( ValidFrom, ValidTo),
  CONSTRAINT PK_ID_ProdName PRIMARY KEY (ID, ProdName)
) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[PriceHistory]));

However, when I insert Data in my table

INSERT INTO [DWH_STORE].[dbo].[Price] (ID, ProdName, Price)
    VALUES (12, 'Banana', 1)`

SELECT * FROM [DWH_STORE].[dbo].[Price]

I get

result

with column 'ValidFrom' as '2017-08-09 11:18:30'. Why I do not get the default value '1900-01-01 00:00:00' for that column as expected?

My Sysdatetime() at the time of insert was '2017-08-09 13:18:30'

I have tried to set DEFAULT as Sysdatetime() to get current date and time, and that does not work either. It seems that If I set DEFAULT to whatever I always get the same result.

I appreciate every answer.

Solution

  • I got with column 'ValidFrom' as '2017-08-09 11:18:30'. Why I do not get the default value '1900-01-01 00:00:00' for that column as expected?

    It is working as expected. Your assumptions are wrong. From Temporal Tables:

    INSERTS:

    On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. This marks the row as open.