Protecting data from unauthorized access is a major challenge for organizations today. SQL Server 2022 introduces Database Ledger, a new feature that enhances data security. It protects data from attackers and high-privileged users like DBAs, system administrators, and cloud administrators.

The Database Ledger works like a traditional ledger, recording historical data. When a row is updated, SQL Server stores its previous value in a history table. This feature uses blockchain technology to ensure cryptographic data integrity. SQL Server hashes each transaction with SHA-256, generates a root hash and then links it to the previous block’s hash - forming a secure chain of records.

In this article, we will learn about this new feature of SQL Server through a practical demonstration.

Ledger Database

SQL 2022 introduces Ledger Databases, designed specifically to store ledger tables. Ledger databases use blockchain technology, making them tamper-evident, cryptographically secure, integrated, and transparent. It is useful for applications that require audit trails as well as a high level of security, trust, and compliance.

Note: When you create a table inside a ledger database, SQL Server always creates it as a ledger table.

You enable this with the WITH LEDGER = ON option while creating the database:

CREATE DATABASE [SQL2022-LedgerDB] WITH LEDGER = ON

Alternatively, if you are create the database using GUI, set Is Ledger Database = True.

Ledger Tables

SQL Server 2022 introduces Ledger Tables, a special type of table that ensures immutable and tamper-evident data by cryptographically linking transactions for validation. Along with Ledge Databases, they form the SQL Server Ledger feature.

Note: By default, all tables inside a ledger database are ledger tables. If you want to create a ledger table inside a regular(non-ledger) database, you must explicitly enable it using WITH(LEDGER = ON)

There are two types of ledger tables: Updatable and Append Only.

Updatable Ledger Tables

These allows updates and modifications. When you create an Updateable Ledger Table, SQL Server automatically creates a history table that records every row version. This type is useful in scenarios where data changes frequently, such as payment status or customer information.

SQL Server also creates a Ledger View that joins the main table with its history table, making it easy to view all changes in one place.

Below example creates an Updateable Ledger Table:

CREATE TABLE dbo.UpdatableLedger (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100)
)
WITH (
        SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[UpdatableLedger_History])
        , LEDGER = ON (LEDGER_VIEW = [dbo].[UpdatableLedger_LedgerView])
);

Explanation of syntax:

• SYSTEM_VERSIONING = ON makes this table a system-versioned table. A system versioned table, introduced in SQL 2016, keeps track of changes made on the table over a time. It does so by maintaining a history table which keeps record of all the changes and the main table which keeps latest data. SQL Server Ledger is build on this feature.
• HISTORY_TABLE explicitly names the history table. If not used, SQL Server generates a name.
• WITH (LEDGER = ON) enables ledger feature making the table tamper-evident. It also records changes in ledger metadata such as sys.database_ledger_transactions, sys.database_ledger_blocks and sys.database_ledger_digest_locations.
• LEDGER_VIEW explicitly names the ledger view. If not used, SQL Server generates a name.

You can see the results in the image below. Note the additional columns SQL Server automatically adds to your table:

• ledger_start_transaction_id (BIGINT, NOT NULL) – transaction ID that inserts or modifies a row.
• ledger_end_transaction_id (BIGINT, NULL) – transaction ID that deletes or modifies a row.
• ledger_start_sequence_number (BIGINT, NOT NULL) – sequence number of the operation within the transaction.
• ledger_end_sequence_number (BIGINT, NULL) – sequence number marking the row’s validity end.

Example - Updatable Ledger Tables

In the following example, we create an updatable ledger table named CustomerLedger. We insert, update, and delete a few records to observe the behavior. Finally, we query the ledger view to review the complete history of all transactions on the table.

First, we create the updatable ledger table.

CREATE TABLE dbo.CustomerLedger (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    City NVARCHAR(100)
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerLedger_History),
    LEDGER = ON (LEDGER_VIEW = dbo.CustomerLedger_LedgerView)
);

Next, we insert few records in the table

--1st Transaction
INSERT INTO dbo.CustomerLedger (CustomerID, Name, City)
VALUES (1, 'Mike', 'Delhi'),
       (2, 'Matt', 'Mumbai'),
       (3,'Robin','Sydney'),
       (4,'Hailey','New Jersey')
GO
-- 2nd Transaction
INSERT INTO dbo.CustomerLedger (CustomerID, Name, City)
VALUES (5, 'Kyle', 'Texas')
GO

Let's check the inserted data

SELECT [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger]

Here are a few observations on the inserted data:

• The first four rows shares the same transaction id of 1189 in the ledger_start_transaction_id column which confirms that first insert statement created these rows.
• It captured order of insertion of Customer Id as 1 -> 2 -> 3 -> 4 in the ledger_start_sequence_number column with values 0 -> 1 -> 2 -> 3.
• Fifth row 'Customer Id - 5' has transaction id of 1192 confirming that second insert statement created it. The value 0 value in the ledger_start_sequence_number column further confirms that this transaction inserted only one.
• Because no updates or deletions have occurred yet, the ledger_end_transaction_id and ledger_end_sequence_number remains NULL.

Now we update a row and check the ledger and ledger history table.

UPDATE dbo.CustomerLedger
SET City = 'Chennai'
WHERE CustomerID = 2;
GO

SELECT [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger]

SELECT TOP (1000) [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger_History]

Observations after updating the record:

• The ledger_start_transaction_id of the second row (Customer Id 2) is now changed to 1194 after the update statement. It's previous value of 1189 is inserted into history table. Similarly the ledger_start_sequence_number is updated to 0 as this is the only statement in the transaction and it's previous value of 1 is moved to history table.
• The ledger_end_transaction_id is set to 1194 in the history table which confirms that this row is modified by this update statement only.

Next, delete a record and again check the ledger and ledger history table

DELETE FROM dbo.CustomerLedger
WHERE CustomerID = 1;
GO

Observations after deleting the record:

• The record of Customer ID 1 is deleted from the main ledger table and moved to history table.
• The ledger_start_transaction_id is set to 1189(original transaction id) and ledger_end_transaction_id is set to 1203(current transaction id) in the history table. This means that data was inserted in the main ledger table by 1189 transaction id and deleted by 1203 transaction id.

We also check the Ledger View

SELECT TOP (1000) [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_transaction_id]
      ,[ledger_sequence_number]
      ,[ledger_operation_type]
      ,[ledger_operation_type_desc]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger_LedgerView]
  ORDER BY [ledger_transaction_id]

We see these results. The ledger view shows the complete sequence of events that occurred on the ledger table. Notice that SQL Server treats an UPDATE statement as a combination of INSERT + DELETE operations (Transaction ID 1194).

Append-Only Ledger Tables

Append-Only Ledger Tables allows only inserts. They don't support updates or deletes, so SQL Server does not create a history table for them. These tables are useful in scenarios for audit trails and logs capturing. Like Updatable Ledger Tables, SQL Server creates a Ledger View for Append-Only Ledger Tables also.

The below example creates an Append-Only Ledger Table:

CREATE TABLE dbo.AppendOnlyLedger (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100)
)
WITH (
       LEDGER = ON (
                    APPEND_ONLY = ON,
                    LEDGER_VIEW = [dbo].[AppendOnlyLedger_LedgerView])
);

Explanation of syntax:

• WITH (LEDGER = ON) enables ledger feature and makes the table tamper-evident.
• APPEND_ONLY = ON ensures you only insert data, making the table immutable.
• LEDGER_VIEW explicitly names the ledger view.

Additional Columns SQL Server adds automatically:

• ledger_start_transaction_id (BIGINT, NOT NULL) – transaction ID that inserts a row.
• ledger_start_sequence_number (BIGINT, NOT NULL) – sequence number of the operation within the transaction.

Sine no updates or deletes occur, SQL Server does not add ledger_end_transaction_id and ledger_end_sequence_number columns.

Example - Append-Only Ledger Tables

In the following example, we create an append-only ledger table named 'AuditLedger'. We insert a few records into the table and then attempt to update and delete some rows to observe the behavior. Finally, we query the ledger view to review the history of all transactions performed on the table.

Create the append-only ledger table with this code:

CREATE TABLE dbo.AuditLedger (
    AuditID INT PRIMARY KEY,
    Action NVARCHAR(100),
    ActionTime DATETIME2 DEFAULT SYSUTCDATETIME()
)
WITH (
    LEDGER = ON (APPEND_ONLY = ON, LEDGER_VIEW = dbo.AuditLedger_LedgerView)
);

Now, insert few records in the table.

--1st Transaction
INSERT INTO dbo.AuditLedger (AuditID, Action)
VALUES (1, 'Login by user A'),
       (2, 'Password change by user B')
GO

--2nd Transaction
INSERT INTO dbo.AuditLedger (AuditID, Action)
VALUES (3, 'Logout by user D')
GO

Check the inserted data.

SELECT TOP (1000) [AuditID]
      ,[Action]
      ,[ActionTime]
      ,[ledger_start_transaction_id]
      ,[ledger_start_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[AuditLedger]

The behavior of insertion is same as updatable ledger tables. Let us update or delete a few records.

UPDATE dbo.AuditLedger
SET Action = 'Login by user X'
WHERE AuditID = 1
GO

DELETE FROM dbo.AuditLedger
WHERE AuditID = 2
GO

When we tried to update or delete records, SQL Server returned this error - 'Updates are not allowed for the append only Ledger table 'dbo.AuditLedger'. This confirms that append-only ledger tables are immutable and tamper-evident. Once you insert any data into these tables, you can cannot modify or delete the content.

Check the Ledger View

SELECT TOP (1000) [AuditID]
      ,[Action]
      ,[ActionTime]
      ,[ledger_transaction_id]
      ,[ledger_sequence_number]
      ,[ledger_operation_type]
      ,[ledger_operation_type_desc]
  FROM [SQL2022-LedgerDB].[dbo].[AuditLedger_LedgerView]

In the screenshot below, the ledger view displays the complete sequence of events that occurred on the ledger table. It shows the transaction ID, operation type, and sequence number for each row, which makes the table tamper-evident.

Important Considerations Before Using Database Ledger

Although SQL Server Ledger offers strong benefits, you should consider the downsides before enabling this:

• Ever-Increasing Storage: History tables records every version of rows, so storage grows quickly in write-heavy workloads.
• Performance Impact: Each modifications requires additional writes to history table, metadata, and cryptographic hashing. This makes ledger tables slower than normal tables and not suitable for OLTP workloads.
• Limited Flexibility: You cannot create normal tables inside a ledger database. Since ledger tables mainly server audit and compliance scenarios, their scope is limited.

Conclusion

In this article, we explored the SQL Server Ledger feature in SQL 2022. We learned how ledger databases and ledger tables work, and we demonstrated their through examples. By careful weighing the trade-offs, organizations can use this feature to enhance data security, auditability, and compliance.