How to Create Ledger Tables in Azure SQL: Step by Step

Ledger tables in Azure SQL keep a cryptographically protected history of every change made to your data. Creating Ledger tables in Azure SQL lets you prove to an auditor that no record was tampered with, without writing a single line of application code: the engine itself records every INSERT, UPDATE and DELETE.

Prerequisites

• A database in Azure SQL Database (the feature also exists in SQL Server 2022 and later).
• Permissions to create tables (for example, membership of db_owner).
• SQL Server Management Studio (SSMS) or Azure Data Studio connected to the database.
• Basic T-SQL knowledge.

Step 1: Choose the Ledger table type

There are two types, and the choice depends on what the table needs to do:

• Updatable ledger table — accepts INSERT, UPDATE and DELETE. Every change is recorded in a history table and exposed through a Ledger view.
• Append-only ledger table — accepts INSERT only. Any attempt to UPDATE or DELETE is blocked by the engine. Ideal for event logs or access records.

For business data that changes (payments, contracts, balances) use the updatable version. For data that should never change, use append-only.

Step 2: Create an updatable Ledger table

Creating one is a normal CREATE TABLE with the LEDGER = ON clause. It pays to name the history table and the Ledger view explicitly, so you can find them easily later:

CREATE TABLE dbo.Pagamentos
(
    PagamentoID INT IDENTITY(1,1) PRIMARY KEY,
    Cliente     NVARCHAR(100) NOT NULL,
    Valor       DECIMAL(10,2) NOT NULL,
    Estado      NVARCHAR(20)  NOT NULL
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Pagamentos_History),
    LEDGER = ON (LEDGER_VIEW = dbo.Pagamentos_Ledger)
);

The engine automatically adds system columns that identify the transaction that inserted or deleted each row version.

Step 3: Insert and change data

From here on you work with the table like any other. No special syntax is required:

INSERT INTO dbo.Pagamentos (Cliente, Valor, Estado)
VALUES (N'Ana Silva', 250.00, N'Pendente');

UPDATE dbo.Pagamentos
SET Estado = N'Pago'
WHERE Cliente = N'Ana Silva';

Step 4: Query the history in the Ledger view

The Ledger view shows what happened to each row over time. An UPDATE appears as a pair of operations: the old version is marked as DELETE and the new one as INSERT.

SELECT Cliente,
       Valor,
       Estado,
       ledger_transaction_id,
       ledger_sequence_number,
       ledger_operation_type_desc
FROM dbo.Pagamentos_Ledger
ORDER BY ledger_transaction_id, ledger_sequence_number;

It is this INSERT/DELETE pair that lets you reconstruct the value of any row at any point in the past.

Step 5: Find out who ran each transaction

The system view sys.database_ledger_transactions stores the author and the time of every recorded transaction:

SELECT t.transaction_id,
       t.principal_name,
       t.commit_time
FROM sys.database_ledger_transactions AS t
ORDER BY t.commit_time DESC;

By joining transaction_id with ledger_transaction_id from the Ledger view, you learn who changed what and when.

Step 6: Create an append-only table

For records that must never be altered, such as an access log:

CREATE TABLE dbo.Acessos
(
    AcessoID  INT IDENTITY(1,1) PRIMARY KEY,
    Utilizador NVARCHAR(100) NOT NULL,
    DataHora   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

Verify the result

First, confirm the tables were created with the right Ledger type:

SELECT name, ledger_type_desc
FROM sys.tables
WHERE ledger_type_desc IS NOT NULL;

Then run the test that really matters: try to change a row in the append-only table.

UPDATE dbo.Acessos SET Utilizador = N'outro' WHERE AcessoID = 1;

The engine returns an error and the operation does not happen — not even the database administrator can force it. In the updatable table the UPDATE succeeds, but the previous version remains visible in the dbo.Pagamentos_Ledger view. If both things happen, everything is working.

Conclusion

In a few minutes you gained, inside the database, an integrity guarantee that used to require triggers, hand-made audit tables and a lot of trust. The next step is to automate the generation of digests to immutable storage and then use the Ledger verification procedures to prove, mathematically, that the history was never touched. One final tip: a Ledger table does not disappear with a DROP TABLE — it is marked as dropped and the history is kept. Which of your current tables would benefit most from becoming append-only?