Partition with temporal tables
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
You can use partitioning on both the current and the history table independently. However, partitioning can't be used to change the content of the data without system-versioning.
Partitioning is an Enterprise edition feature in SQL Server 2016 (13.x) before Service Pack 1 and earlier versions. Partitioning is supported in all editions in SQL Server 2016 (13.x) with Service Pack 1, and later versions.
Partition temporal tables
This section describes how to use SWITCH IN
and SWITCH OUT
with temporal tables.
Current table
SWITCH IN
to the current table can be used to facilitate data loading and querying while SYSTEM_VERSIONING
is ON
.
SWITCH OUT
isn't permitted while SYSTEM_VERSIONING
is ON
.
History table
You can run SWITCH OUT
from the history table while SYSTEM_VERSIONING
is ON
, to purge portions of history data that is no longer relevant.
SWITCH IN
isn't allowed while SYSTEM_VERSIONING
is ON
, since it can invalidate temporal data consistency.
Related content
- Temporal tables
- Get started with system-versioned temporal tables
- Temporal table system consistency checks
- 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