Getting Started with system-versioned temporal tables

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

Depending on your scenario, you can either create new system-versioned temporal tables or modify existing ones by adding temporal attributes to the existing table schema. When the data in temporal table is modified, the system builds version history transparently to applications and end users. As a result, working with system-versioned temporal tables doesn't require any change to the way table is modified or how the latest (current) state of the data is queried.

In addition to regular DML and querying, temporal also provides convenient and easy ways to get insights from data history through extended Transact-SQL syntax. Every system-versioned table has a history table assigned but it is completely transparent for the users unless they want to optimize workload performance or storage footprint by creating additional indexes or choosing different storage options.

The following diagram depicts typical workflow with system-versioned temporal tables:

This article is divided into the following five sections:

• Creating a System-Versioned Temporal Table
• Modifying Data in a System-Versioned Temporal Table
• Querying Data in a System-Versioned Temporal Table
• Changing the Schema of a System-Versioned Temporal Table
• Stopping System-Versioning on a System-Versioned Temporal Table

Next steps

• Temporal Tables
• Temporal Table System Consistency Checks
• Partitioning with Temporal Tables
• Temporal Table Considerations and Limitations
• Temporal Table Security
• Manage Retention of Historical Data in System-Versioned Temporal Tables
• System-Versioned Temporal Tables with Memory-Optimized Tables
• Temporal Table Metadata Views and Functions