Create and use updatable ledger tables
Applies to: SQL Server 2022 (16.x) Preview Azure SQL Database
This article shows you how to create an updatable ledger table. Next, you'll insert values in your updatable ledger table and then make updates to the data. Finally, you'll view the results by using the ledger view. We'll use an example of a banking application that tracks banking customers' balances in their accounts. Our example will give you a practical look at the relationship between the updatable ledger table and its corresponding history table and ledger view.
Create an updatable ledger table
We'll create an account balance table with the following schema.
|Column name||Data type||Description|
|CustomerID||int||Customer ID - Primary key clustered|
|LastName||varchar (50)||Customer last name|
|FirstName||varchar (50)||Customer first name|
|Balance||decimal (10,2)||Account balance|
Creating updatable ledger tables requires the ENABLE LEDGER permission. For more information on permissions related to ledger tables, see Permissions.
CREATE SCHEMA [Account]; GO CREATE TABLE [Account].[Balance] ( [CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED, [LastName] VARCHAR (50) NOT NULL, [FirstName] VARCHAR (50) NOT NULL, [Balance] DECIMAL (10,2) NOT NULL ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Account].[BalanceHistory]), LEDGER = ON );
LEDGER = ONargument is optional if you enabled a ledger database when you created your database.
When your updatable ledger table is created, the corresponding history table and ledger view are also created. Run the following T-SQL commands to see the new table and the new view.
SELECT ts.[name] + '.' + t.[name] AS [ledger_table_name] , hs.[name] + '.' + h.[name] AS [history_table_name] , vs.[name] + '.' + v.[name] AS [ledger_view_name] FROM sys.tables AS t JOIN sys.tables AS h ON (h.[object_id] = t.[history_table_id]) JOIN sys.views v ON (v.[object_id] = t.[ledger_view_id]) JOIN sys.schemas ts ON (ts.[schema_id] = t.[schema_id]) JOIN sys.schemas hs ON (hs.[schema_id] = h.[schema_id]) JOIN sys.schemas vs ON (vs.[schema_id] = v.[schema_id]) WHERE t.[name] = 'Balance';
Insert the name
Nick Jonesas a new customer with an opening balance of $50.
INSERT INTO [Account].[Balance] VALUES (1, 'Jones', 'Nick', 50);
Insert the names
Joe Smith, and
Mary Michaelsas new customers with opening balances of $500, $30, and $200, respectively.
INSERT INTO [Account].[Balance] VALUES (2, 'Smith', 'John', 500), (3, 'Smith', 'Joe', 30), (4, 'Michaels', 'Mary', 200);
[Account].[Balance]updatable ledger table, and specify the GENERATED ALWAYS columns added to the table.
SELECT [CustomerID] ,[LastName] ,[FirstName] ,[Balance] ,[ledger_start_transaction_id] ,[ledger_end_transaction_id] ,[ledger_start_sequence_number] ,[ledger_end_sequence_number] FROM [Account].[Balance];
In the results window, you'll first see the values inserted by your T-SQL commands, along with the system metadata that's used for data lineage purposes.
ledger_start_transaction_idcolumn notes the unique transaction ID associated with the transaction that inserted the data. Because
Marywere inserted by using the same transaction, they share the same transaction ID.
ledger_start_sequence_numbercolumn notes the order by which values were inserted by the transaction.
Nick's balance from
UPDATE [Account].[Balance] SET [Balance] = 100 WHERE [CustomerID] = 1;
[Account].[Balance]ledger view, along with the transaction ledger system view to identify users that made the changes.
SELECT t.[commit_time] AS [CommitTime] , t.[principal_name] AS [UserName] , l.[CustomerID] , l.[LastName] , l.[FirstName] , l.[Balance] , l.[ledger_operation_type_desc] AS Operation FROM [Account].[Balance_Ledger] l JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id ORDER BY t.commit_time DESC;
Nick's account balance was successfully updated in the updatable ledger table to
The ledger view shows that updating the ledger table is a
DELETEof the original row with
50. The balance with a corresponding
INSERTof a new row with
100shows the new balance for
Submit and view feedback for