Morteza Jangjoo

Posted on Sep 17

SQL Server Ledger Tables: Complete Guide with Banking Example

#sqlserver #database #blockchain #security

SQL Server Ledger Tables: A Complete Guide with Banking Example

Introduction

Data integrity and tamper-proof auditing are critical in financial, healthcare, and government applications. Starting from SQL Server 2022, Microsoft introduced Ledger Tables, a blockchain-like feature that guarantees immutability and cryptographic proof of your data.

With Ledger, every change is cryptographically linked and verifiable, ensuring that nobody can secretly alter your records. This article explores Ledger Tables in detail with practical SQL scripts and a real-world banking scenario.

Types of Ledger Tables

SQL Server supports two kinds of Ledger Tables:

Updatable Ledger Tables
- Store the current data plus a hidden history table that records every change.
- Support INSERT, UPDATE, and DELETE.
- Ideal for systems where records evolve but a tamper-proof audit trail is required (e.g., bank accounts).
Append-only Ledger Tables
- Allow only INSERT.
- Great for logging immutable data such as transactions or audit logs.

Ledger Internals

History Tables: Hidden system tables (mssql_ledger_history_for_<TableName>_<GUID>) that store row versions with transaction IDs.
Database Ledger: Tracks digests for all ledger tables at the database level.
Digest: A cryptographic hash that can be exported and verified later to prove no tampering has occurred.

Step 1 – Create a Database

CREATE DATABASE BankLedgerDemo;
GO
USE BankLedgerDemo;
GO

Step 2 – Create Ledger Tables

Accounts (Updatable Ledger)

CREATE TABLE Accounts
(
    AccountId INT PRIMARY KEY,
    HolderName NVARCHAR(100),
    Balance DECIMAL(18,2) NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = OFF));

Transactions (Append-only Ledger)

CREATE TABLE Transactions
(
    TransactionId INT PRIMARY KEY,
    FromAccountId INT NULL,
    ToAccountId INT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    TransactionType NVARCHAR(20) NOT NULL, -- Deposit, Withdraw, Transfer
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

Step 3 – Insert Accounts

INSERT INTO Accounts (AccountId, HolderName, Balance)
VALUES (1, N'Morteza', 1000.00),
       (2, N'Ali', 500.00);

Step 4 – Perform Banking Operations

Deposit

BEGIN TRAN;
    UPDATE Accounts
    SET Balance = Balance + 300
    WHERE AccountId = 1;

    INSERT INTO Transactions (TransactionId, ToAccountId, Amount, TransactionType)
    VALUES (1, 1, 300, 'Deposit');
COMMIT;

Withdraw

BEGIN TRAN;
    UPDATE Accounts
    SET Balance = Balance - 200
    WHERE AccountId = 1;

    INSERT INTO Transactions (TransactionId, FromAccountId, Amount, TransactionType)
    VALUES (2, 1, 200, 'Withdraw');
COMMIT;

Transfer

BEGIN TRAN;
    UPDATE Accounts
    SET Balance = Balance - 150
    WHERE AccountId = 1;

    UPDATE Accounts
    SET Balance = Balance + 150
    WHERE AccountId = 2;

    INSERT INTO Transactions (TransactionId, FromAccountId, ToAccountId, Amount, TransactionType)
    VALUES (3, 1, 2, 150, 'Transfer');
COMMIT;

Step 5 – View Current Data

SELECT * FROM Accounts;
SELECT * FROM Transactions;

Step 6 – Explore Ledger History

Find the History Table

SELECT name 
FROM sys.tables
WHERE name LIKE 'mssql_ledger_history_for_Accounts%';

Query Record History

SELECT AccountId, HolderName, Balance,
       ledger_start_transaction_id, ledger_end_transaction_id
FROM [mssql_ledger_history_for_Accounts_<GUID>]
WHERE AccountId = 1
ORDER BY ledger_start_transaction_id;

Step 7 – Detect Record Changes

Each row version in the history table tells you what changed and when. For example:

AccountId	HolderName	Balance	ledger_start_transaction_id	ledger_end_transaction_id
1	Morteza	1000.00	1	2
1	Morteza	1300.00	2	3
1	Morteza	1100.00	3	NULL

📌 Interpretation:

Balance started at 1000.
Increased to 1300 after deposit.
Dropped to 1100 after withdrawal.

Step 8 – Compare Current and Historical Values

SELECT h.AccountId, h.Balance AS OldBalance, 
       a.Balance AS CurrentBalance,
       h.ledger_start_transaction_id, h.ledger_end_transaction_id
FROM [mssql_ledger_history_for_Accounts_<GUID>] h
JOIN Accounts a ON h.AccountId = a.AccountId
WHERE h.AccountId = 1;

Step 9 – Generate Digest (Proof)

EXEC sp_generate_database_ledger_digest;

This cryptographic digest can be exported to external storage or blockchain for independent verification.

Conclusion

SQL Server Ledger Tables bring blockchain-like immutability into traditional relational databases. With Ledger, you can:

Prevent tampering with sensitive data.
Prove data integrity to auditors, regulators, or courts.
Build trust in financial and mission-critical systems.

In this article, we implemented a banking system with deposits, withdrawals, and transfers using Ledger Tables and demonstrated how to track record changes and generate cryptographic proofs.

If you’re working with SQL Server 2022 or later, Ledger Tables are a must-explore feature for any application where data trustworthiness is non-negotiable.

I’m Morteza Jangjoo and “Explaining things I wish someone had explained to me”

DEV Community