How to Enable System-Versioning on an Existing Table

Enabling System-Versioning on an Existing Table

Write a SQL query to enable system-versioning on an existing table.

Solution:

-- Enable system-versioning on an existing table.
ALTER TABLE Employees
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT '9999-12-31 23:59:59.9999999',
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to enable system-versioning on an existing table.
• Key Components :
• ADD SysStartTime and SysEndTime: Adds columns for tracking validity periods.
• PERIOD FOR SYSTEM_TIME: Defines the time period for versioning.
• SYSTEM_VERSIONING = ON: Enables system-versioning and links to a history table.
• Why Enable System-Versioning? :
• Enabling system-versioning retroactively adds historical tracking to an existing table.
• It ensures compliance with auditing requirements.
• Real-World Application :
• In legacy systems, enabling versioning adds auditing capabilities without redesigning the schema.

Additional Notes:

• Ensure that the table has a primary key before enabling system-versioning.
• Test thoroughly to ensure compatibility with existing queries.
• Important Considerations:
• Monitor storage usage after enabling versioning.

For more Practice: Solve these Related Problems:

• Write a SQL query to enable system-versioning on an existing Orders table, linking it to a history table named OrderHistory.
• Write a SQL query to add temporal capabilities to a legacy Products table for tracking price changes over time.
• Write a SQL query to enable versioning on a Customers table to maintain a history of address updates.
• Write a SQL query to retroactively enable system-versioning on an Inventory table for auditing stock level changes.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Disabling System-Versioning on a Temporal Table.
Next SQL Exercise: Archiving Old Records from a History Table.