Bewerken

Append-only ledger tables

  • Artikel

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

Append-only ledger tables allow only INSERT operations on your tables, which ensure that privileged users such as database administrators can't alter data through traditional Data Manipulation Language operations. Append-only ledger tables are ideal for systems that don't update or delete records, such as security information event and management systems or blockchain systems where data needs to be replicated from the blockchain to a database. Because there are no UPDATE or DELETE operations on an append-only table, there's no need for a corresponding history table as there is with updatable ledger tables.

Diagram that shows architecture of ledger tables.

You can create an append-only ledger table by specifying the LEDGER = ON argument in your CREATE TABLE (Transact-SQL) statement and specifying the APPEND_ONLY = ON option.

Important

After a table is created as a ledger table, it can't be reverted to a table that doesn't have ledger functionality. As a result, an attacker can't temporarily remove ledger capabilities, make changes to the table, and then reenable ledger functionality.

Append-only ledger table schema

An append-only table needs to have the following GENERATED ALWAYS columns that contain metadata noting which transactions made changes to the table and the order of operations by which rows were updated by the transaction. When you create an append-only ledger table, GENERATED ALWAYS columns will be created in your ledger table. This data is useful for forensics purposes in understanding how data was inserted over time.

If you don't specify the definitions of the GENERATED ALWAYS columns in the CREATE TABLE statement, the system automatically adds them by using the following default names.

Default column name Data type Description
ledger_start_transaction_id bigint The ID of the transaction that created a row version
ledger_start_sequence_number bigint The sequence number of an operation within a transaction that created a row version

Ledger view

For every append-only ledger table, the system automatically generates a view, called the ledger view. The ledger view reports all row inserts that have occurred on the table. The ledger view is primarily helpful for updatable ledger tables, rather than append-only ledger tables, because append-only ledger tables don't have any UPDATE or DELETE capabilities. The ledger view for append-only ledger tables is available for consistency between both updatable and append-only ledger tables.

Ledger view schema

Note

The ledger view column names can be customized when you create the table by using the <ledger_view_option> parameter with the CREATE TABLE (Transact-SQL) statement. For more information, see ledger view options and the corresponding examples in CREATE TABLE (Transact-SQL).

Default column name Data type Description
ledger_transaction_id bigint The ID of the transaction that created or deleted a row version.
ledger_sequence_number bigint The sequence number of a row-level operation within the transaction on the table.
ledger_operation_type tinyint Contains 1 (INSERT) or 2 (DELETE). Inserting a row into the ledger table produces a new row in the ledger view that contains 1 in this column. Deleting a row from the ledger table produces a new row in the ledger view that contains 2 in this column. Updating a row in the ledger table produces two new rows in the ledger view. One row contains 2 (DELETE), and the other row contains 1 (INSERT) in this column. A DELETE shouldn't occur on an append-only ledger table.
ledger_operation_type_desc nvarchar(128) Contains INSERT or DELETE. For more information, see the preceding row.

Related content