Partitioning with Temporal Tables

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

You can use partitioning on both the current and the history table independently. However, partitioning cannot be used to change the content of the data without system-versioning.

Note

Partitioning is an Enterprise Edition feature in SQL Server 2016 prior to Service Pack 1 and earlier versions. Partitioning is supported in all editions in SQL Server 2016 Service Pack 1 and later versions.

  • Current Table:

    • SWITCH IN to the current table can be used to facilitate data loading and querying while SYSTEM_VERSIONING is ON

    • SWITCH OUT is not permitted while SYSTEM_VERSIONING is ON

  • History Table:

    • SWITCH OUT from history table can performed while SYSTEM_VERSIONING is ON to purge portions of history data that is no longer relevant.

    • SWITCH IN is not allowed while SYSTEM_VERSIONING is ON since it can invalidate temporal data consistency.

Did this Article Help You? We’re Listening

What information are you looking for, and did you find it? We’re listening to your feedback to improve the content. Please submit your comments to sqlfeedback@microsoft.com

See Also

Temporal Tables
Getting 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