Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance
There are some considerations and limitations to be aware of when working with ledger tables due to the nature of system-versioning and immutable data.
Consider the following when working with ledger.
APPEND_ONLY = ON
clause) in the database will be updatable ledger tables. To create append-only ledger tables, use the APPEND_ONLY = ON
clause in the CREATE TABLE (Transact-SQL) statements.TRUNCATE TABLE
isn't supported.Updatable ledger tables are based on the technology of temporal tables and inherit most of the limitations but not all of them. Below is a list of limitations that is inherited from temporal tables.
(n)varchar(max)
, varbinary(max)
, (n)text
, and image
, they'll incur significant storage costs and have performance implications due to their size. As such, when designing your system, care should be taken when using these data types.WITH (ONLINE = ON
) has no effect on ALTER TABLE ALTER COLUMN
in case of system-versioned temporal table. ALTER COLUMN
isn't performed as online regardless of which value was specified for the ONLINE
option.INSERT
and UPDATE
statements can't reference the GENERATED ALWAYS columns. Attempts to insert values directly into these columns will be blocked.UPDATETEXT
and WRITETEXT
aren't supported.Adding nullable columns is supported. Adding non-nullable columns is not supported. Ledger is designed to ignore NULL values when computing the hash of a row version. Based on that, when a nullable column is added, ledger will modify the schema of the ledger and history tables to include the new column, however, this doesn't impact the hashes of existing rows. Adding columns in ledger tables is captured in sys.ledger_column_history.
Normally, dropping a column or table completely erases the underlying data from the database and is fundamentally incompatible with the ledger functionality that requires data to be immutable. Instead of deleting the data, ledger simply renames the objects being dropped so that they're logically removed from the user schema, but physically remain in the database. Any dropped columns are also hidden from the ledger table schema, so that they're invisible to the user application. However, the data of such dropped objects remains available for the ledger verification process, and allows users to inspect any historical data through the corresponding ledger views. Dropping columns in ledger tables is captured in sys.ledger_column_history. Dropping a ledger table is captured in sys.ledger_table_history. Dropping ledger tables and its dependent objects are marked as dropped in system catalog views and renamed:
is_dropped_ledger_table
in sys.tables and renamed using the following format: MSSQL_DroppedLedgerTable_<dropped_ledger_table_name>_<GUID>
.MSSQL_DroppedLedgerHistory_<dropped_history_table_name>_<GUID>
.is_dropped_ledger_view
in sys.views and renamed using the following format: MSSQL_DroppedLedgerView_<dropped_ledger_view_name>_<GUID>
.Note
The name of dropped ledger tables, history tables and ledger views might be truncated if the length of the renamed table or view exceeds 128 characters.
Any changes that don't impact the underlying data of a ledger table are supported without any special handling as they don't impact the hashes being captured in the ledger. These changes include:
However, any operations that might affect the format of existing data, such as changing the data type aren't supported.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayTraining
Module
Explore new capabilities in security, scalability, and availability in SQL Server 2022 - Training
This module introduces new capabilities in security, scalability, and availability in SQL Server 2022.