SQL 2025 master class LAB 19 Ledger

-- with thanks and acknowlegement https://www.sqlservercentral.com/articles/database-ledger-in-sql-server-2022

--Step 1

CREATE DATABASE [LedgerDB] WITH LEDGER = ON

GO 

USE LedgerDB 

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)
);

--step 2

--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

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

-- step 3

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 [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 [LedgerDB].[dbo].[CustomerLedger_History] 

--step 4

DELETE FROM dbo.CustomerLedger
WHERE CustomerID = 1;
GO

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

 --housekeeping

use master

ALTER DATABASE LedgerDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE LedgerDB;
GO