Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

Recently, my client required that I enable versioning for one of the critical tables in my SQL server database. I tried running the SQL query below to enable versioning but encountered this error. In this article, I will walk you through the complete steps to fix this issue.

ALTER TABLE AzureProduct
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AzureProduct))

I tried running the above query, but then I got this error. The screenshot below is for your reference.

Cause of the Error

I got this error because I tried enabling system versioning without adequately setting up the SYSTEM_TIME period on the table.

Solution

Below are the steps I followed to fix this error.

1. First, we need to add the period columns without adding the GENERATED ALWAYS using the below query.

ALTER TABLE Product ADD
    ValidFrom DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidTo DATETIME2(7) NOT NULL DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999');

The command was executed successfully after executing the above query, as shown below.

2. We can execute the below query to Add PERIOD FOR SYSTEM_TIME definition.

ALTER TABLE Product
ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

The command was executed successfully after executing the above query, as shown below.

3. Now, convert the columns to GENERATED ALWAYS using the query below.

ALTER TABLE Product 
ALTER COLUMN ValidFrom ADD HIDDEN;
ALTER TABLE Product 
ALTER COLUMN ValidFrom ADD GENERATED ALWAYS AS ROW START;
ALTER TABLE Product 
ALTER COLUMN ValidTo ADD HIDDEN;
ALTER TABLE Product 
ALTER COLUMN ValidTo ADD GENERATED ALWAYS AS ROW END;

Note: Ensure you are using SQL Server 2016 or the latest version, else you will get the error Incorrect syntax near ‘GENERATED’.

4. Finally, you can use the query below to enable versioning.

ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

Conclusion

You can use the above 4 steps to fix this error. Cannot set SYSTEM_VERSIONING to ON when the SYSTEM_TIME period is not defined.

You may also like following the articles below.

• Incorrect Syntax Near The Keyword ‘Order’
• Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.