Trust is hard to come by, especially when it comes to sensitive data like medical records, security or financial info.
External regulators and clients often demand independent, zero trust verification. They don't just want to know that your policies are being followed, they want undeniable proof.
For this sensitive data, starting from SQL Server 2022 (16.x), you can natively use LEDGER TABLES because the table itself becomes self verifying.
Every time a row is inserted, updated, or deleted, SQL Server cryptographically hashes the transaction and links it into a secure, history backed chain.
This provides an unalterable record of data authenticity that simplifies compliance and prevents tampering with sensitive data. Even with full access, the cryptographic chain ensures that the historical data matches the ledger digest perfectly, making internal audits completely bulletproof.
In this article, I will dive straight into the T-SQL scripts you need to build up ledger systems and verify your database authenticity.
Depending on your specific use case, you can create a LEDGER TABLE only for INSERTS, or one that fully tracks INSERT, UPDATE, and DELETE.
🫡 Case 1: Securing Insert Only Data (APPEND_ONLY)
With the code below, you will create a table that strictly allows only INSERT statements.
If you try to run UPDATE query, SQL Server won't even try to execute it. It will instantly block the transaction and fire back an ledger protection error message.
T-SQL Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.PatientRequests
(
RequestID INT NOT NULL PRIMARY KEY,
RequestNumber VARCHAR(255) NULL,
MedicalProduct VARCHAR(255) NULL,
ClientID VARCHAR(255) NULL,
RequestDate DATETIME NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));
GO
INSERT INTO dbo.PatientRequests (RequestID, RequestNumber, MedicalProduct, ClientID, RequestDate)
VALUES (1, 'REQ-9875', 'Insulin Pump', '0101990710023', GETDATE());
GO
UPDATE dbo.PatientRequests
SET MedicalProduct = 'EpiPen'
WHERE RequestID = 1;
GO
Preview:
🫡 Case 2: Securing Updatable Data (INSERT, UPDATE, DELETE)
OK, for our next scenario things get a bit more messy. Because an updatable ledger automatically generates a separate history table to track every change made to the original table, letting SQL Server build it by default is not always the best option.
You need to keep the growth and size of your tables in mind. To prevent overloading your primary storage, the best approach is to organize your ENV by creating a seperate schema and a separate filegroup for your historical ledger data for the best maintenance. Even better, a solution is to create history tables based on a PARTITION FUNCTION. But I won't cover that here.
First lets create schema and filegroup.
USE [master]
GO
ALTER DATABASE [AdventureWorks2022] ADD FILEGROUP [DATA]
GO
ALTER DATABASE [AdventureWorks2022] ADD FILE ( NAME = N'AdventuerWorks_DATA', FILENAME = N'C:\MSSQL\DATA\AdventuerWorks_DATA.ndf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) TO FILEGROUP [DATA]
GO
USE [AdventureWorks2022]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'HIST')
BEGIN
EXEC('CREATE SCHEMA HIST');
END
GO
Now you can create your table using the code below. I have also included the necessary INSERT, UPDATE, and DELETE statements right after the table creation script so you can test the full lifecycle immediately.
T-SQL Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.PatientRequests_main
(
RequestID INT NOT NULL PRIMARY KEY ON [PRIMARY],
RequestNumber VARCHAR(255) NULL,
MedicalProduct VARCHAR(255) NULL,
ClientID VARCHAR(255) NULL,
RequestDate DATETIME NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = HIST.PatientRequests_hist),
LEDGER = ON
);
GO
ALTER TABLE HIST.PatientRequests_hist
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
DROP INDEX [ix_PatientRequests_hist] ON [HIST].[PatientRequests_hist] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [ix_PatientRequests_hist]
ON HIST.PatientRequests_hist(RequestID, ValidFrom, ValidTo)
ON [DATA];
GO
INSERT INTO dbo.PatientRequests_main (RequestID, RequestNumber, MedicalProduct, ClientID, RequestDate)
VALUES (101, 'REQ-5550', 'Blood Pressure Monitor', 'CLIENT-99', GETDATE());
GO
UPDATE dbo.PatientRequests_main
SET MedicalProduct = 'Glucose Meter'
WHERE RequestID = 101;
GO
DELETE FROM dbo.PatientRequests_main
WHERE RequestID = 101;
GO
😎 END RESULT: Querying and Managing Your Ledger Tables
Immediately after you create your ledger tables, SQL Server automatically generates dedicated ledger views for you. This means that even though you are using history tables, you don’t need to write queries using clauses like FOR SYSTEM_TIME AS OF to show past data. You won't be able to turn off system versioning or make manual adjustments to alter past states. Everything is fully taken care of by SQL Server's engine.
To properly audit your data, you should query the system views that SQL Server automatically creates with the _Ledger suffix for your tables. Here is an example below:
T-SQL Code:
SELECT *
FROM dbo.PatientRequests_Ledger
ORDER BY
ledger_transaction_id ASC,
ledger_sequence_number ASC;
GO
SELECT *
FROM dbo.PatientRequests_main_Ledger
ORDER BY
ledger_transaction_id ASC,
ledger_sequence_number ASC;
GO
Preview:
😎 Generating Fingerprint proof
You can now run this procedure below to generate a Database Ledger Digest. This outputs a cryptographic snapshot representing the state of your database at this exact moment in time.
The digest is something you save externally and later use as evidence to verify that the ledger hasn't been tampered with.
EXEC sp_generate_database_ledger_digest;
When you want to verify it, take the outcome of the procedure and execute it like this,
USE master;
GO
ALTER DATABASE [AdventureWorks2022]
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE [AdventureWorks2022];
GO
DECLARE @ledgerDigest NVARCHAR(MAX) = N'{"database_name":"AdventureWorks2022","block_id":1,"hash":"0xDEB4ADFCC9F06E2FF0155C9D0543B9CBA155052657BEFB0B21887A8AF8BC5900","last_transaction_commit_time":"2026-10-05T20:48:27.4000000","digest_time":"2026-10-05T18:51:12.6864896"}';
EXEC sp_verify_database_ledger @ledgerDigest;
GO
USE master;
GO
ALTER DATABASE [AdventureWorks2022]
SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
To view DATABASE Ledger use this command
SELECT * FROM sys.database_ledger_transactions;
GO
SELECT * FROM sys.database_ledger_blocks;
GO
Microsoft Illustration:
Reference:
Top comments (0)