How to handle existing data while enabling versioning on existing table?

Question:

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.

Answer:

When enabling system-versioning (SYSTEM_VERSIONING = ON) on an existing SQL Server table, the main challenge is that the GENERATED ALWAYS columns for PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) cannot be NULL and must be initialized for existing rows.

To handle this safely, follow these steps:

1. Add temporal columns with default values

ALTER TABLE [dbo].[User] ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_User_ValidFrom DEFAULT SYSUTCDATETIME(), ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_User_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

2. Enable system versioning

ALTER TABLE [dbo].[User] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[UserHistory]));

3. Optional: Remove default constraints

After system versioning is enabled, the ValidFrom and ValidTo values are automatically managed by SQL Server. The default constraints used earlier are no longer necessary and can be safely removed:

ALTER TABLE [dbo].[User] DROP CONSTRAINT DF_User_ValidFrom; 
ALTER TABLE [dbo].[User] DROP CONSTRAINT DF_User_ValidTo;

Leaving unused default constraints in the schema may create confusion and serve no functional purpose once SQL Server takes over managing system time values. Dropping them keeps the schema clean and avoids any ambiguity about how those values are assigned.

4. Let's test on Update and delete rows and make sure that system versioning works:

If you prefer a visual approach to enabling system-versioned temporal tables without writing SQL manually, you can use SQL GUI tools like dbForge. It allows you to manage SYSTEM_VERSIONING, define PERIOD FOR SYSTEM_TIME, and view historical data through an intuitive interface.