One of the new features with SQL Server 2022 is the addition of Ledger Tables. These allow the system to track the history of a table, so we can see any changes made. The Ledger tables can be set up as append only or updatable.
Ledger tables are similar to temporal tables, which also track the history of data changes. In fact, they use the same SYSTEM_VERSIONING setting.
We can also specify a ledger database, where all of the tables are ledger tables. Or we can specify ledger tables one by one.
When we create a Ledger table, a history table will be created and maintained by the SQL Server. There will also be a view created, we’ll use this view rather than the history table to see the history of transactions.
In this example, we’ll create an updatable Ledger table.
Example:
We’ll create an updatable ledger table, specifying the name of the history table that will be created.
DROP TABLE IF EXISTS Test.Inventory;
CREATE TABLE Test.Inventory (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ProductName varchar(30) NOT NULL UNIQUE,
ItemCount int NOT NULL
) WITH (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = Test.InventoryHistory),
LEDGER = ON);
We’ll insert and also update some test records.
INSERT INTO Test.Inventory (ProductName, ItemCount) VALUES
('Barbie', 5),
('Truck', 3),
('Lego Set', 4);
UPDATE Test.Inventory SET ItemCount = ItemCount + 1
WHERE ProductName = 'Barbie';
UPDATE Test.Inventory SET ItemCount = ItemCount - 1
WHERE ProductName = 'Truck';
We can query the Ledger view to see the record history;
SELECT * FROM Test.Inventory_Ledger
ORDER BY ID, ledger_transaction_id,
ledger_sequence_number;
The view includes these system columns:
ledger_transaction_id: The ID number for each transaction
ledger_sequence_number: A number to order each operation within a single transaction
ledger_operation_type: 1 for INSERT and 2 for DELETE
ledger_operation_type_desc: INSERT or DELETE
There are also Database Verification stored procedures that allow us to determine if the chain of transactions has been tampered with.
Links:
Create and use updatable ledger tables
SQL Rob 