I am trying to enable system versioning to an existing table like this

ALTER TABLE [dbo].[User]
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

This fails because the table already has data.

This can be fixed by adding default values to the SYSTEM_TIME columns, like this

ALTER TABLE [dbo].[User]
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
CONSTRAINT DF_User_ValidFrom DEFAULT GETDATE(), -- Default constraint here
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
CONSTRAINT DF_User_ValidTo DEFAULT GETDATE(), -- And here
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

But is this the right approach to handle this situation or is there any better way to handle this?

Also, adding default constraint is dangerous because it will allow any possibility to add rows without to those temporal management columns values and that will not give the clear picture of the changes.

I am trying to enable system versioning to an existing table like this

ALTER TABLE [dbo].[User]
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

This fails because the table already has data.

This can be fixed by adding default values to the SYSTEM_TIME columns, like this

ALTER TABLE [dbo].[User]
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
CONSTRAINT DF_User_ValidFrom DEFAULT GETDATE(), -- Default constraint here
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
CONSTRAINT DF_User_ValidTo DEFAULT GETDATE(), -- And here
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

But is this the right approach to handle this situation or is there any better way to handle this?

Also, adding default constraint is dangerous because it will allow any possibility to add rows without to those temporal management columns values and that will not give the clear picture of the changes.

• sql-server
• temporal-tables

1 Answer 1

is this the right approach to handle this situation or is there any better way to handle this?

It is incomplete; you don't actually enable system versioning here. Also setting both the ValidFrom and ValidTo values to the same time is a poor idea; this means that your row was valid for 1/300th of a second. The ValidTo should be the very last time your data type can hold; 9999-12-31T23:59:59.9999999 for a datetime2(7). For the ValidFrom I suggest using a sysdatetime if you want to store a datetime2(7), rather than GETDATE(). Also, choose a value appropriate for your table to be active from. If that if right now then SYSDATETIME() is appropriate; but you might want a value earlier.

Also the CONSTRAINT is part of the column's definition, not a separate definition. This gives the following:

CREATE TABLE dbo.SystemUser (UserId int IDENTITY CONSTRAINT PK_SystemUser PRIMARY KEY,--User is a reserved keyword, don't use it for object names. 
                             UserName nvarchar(50) NOT NULL);
GO
INSERT INTO dbo.SystemUser (Username )
VALUES(N'Pawan Nogariya');
GO
ALTER TABLE dbo.SystemUser ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START
                                         CONSTRAINT DF_SystemUser_ValidFrom DEFAULT SYSDATETIME(),
                               ValidTo DATETIME2 GENERATED ALWAYS AS ROW END
                                       CONSTRAINT DF_SystemUser_ValidTo DEFAULT CONVERT(datetime2(7), '9999-12-31T23:59:59.9999999'),
                               PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
GO
ALTER TABLE dbo.SystemUser SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.SystemUser)); --You don't need the HISTORY_TABLE clause, but I like to be explicit.
GO

This is also the method denoted in the documentation.

Also, adding default constraint is dangerous because it will allow any possibility to add rows without to those temporal management columns values and that will not give the clear picture of the changes.

I don't follow what you mean here. Once a table is a temporal table, you can't UPDATE/INSERT value into those columns:

UPDATE dbo.SystemUser SET ValidFrom = DATEADD(DAY, -1, SYSDATETIME())
GO

INSERT INTO dbo.SystemUser (Username,ValidFrom)
VALUES(N'Thom A',DATEADD(DAY, 1, SYSDATETIME()));

This produces the errors:

Msg 13537, Level 16, State 1, Line 16
Cannot update GENERATED ALWAYS columns in table 'Sandbox.dbo.SystemUser'.

Msg 13536, Level 16, State 1, Line 19
Cannot insert an explicit value into a GENERATED ALWAYS column in table 'Sandbox.dbo.SystemUser'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

If you are "concerned" about the CONSTRAINTs, however, you can remove them:

ALTER TABLE dbo.SystemUser DROP CONSTRAINT DF_SystemUser_ValidFrom;
ALTER TABLE dbo.SystemUser DROP CONSTRAINT DF_SystemUser_ValidTo;
GO
--Insert a row, then update it
INSERT INTO dbo.SystemUser (Username)
VALUES(N'Larnu');
GO
UPDATE dbo.SystemUser
SET UserName = N'Thom A'
WHERE UserName = N'Larnu';
GO
--Get all the data, including histories
SELECT *
FROM dbo.SystemUser FOR SYSTEM_TIME ALL;