Create and use append-only ledger tables

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

This article shows you how to create an append-only ledger table. Next, you'll insert values in your append-only ledger table and then attempt to make updates to the data. Finally, you'll view the results by using the ledger view. We'll use an example of a card key access system for a facility, which is an append-only system pattern. Our example will give you a practical look at the relationship between the append-only ledger table and its corresponding ledger view.

For more information, see Append-only ledger tables.

Prerequisites

• SQL Server Management Studio or Azure Data Studio.

Create an append-only ledger table

We'll create a KeyCardEvents table with the following schema.

Column name	Data type	Description
EmployeeID	int	The unique ID of the employee accessing the building
AccessOperationDescription	nvarchar (MAX)	The access operation of the employee
Timestamp	datetime2	The date and time the employee accessed the building

1. Use SQL Server Management Studio or Azure Data Studio to create a new schema and table called [AccessControl].[KeyCardEvents].

   SQL

   CREATE SCHEMA [AccessControl];
   GO
   CREATE TABLE [AccessControl].[KeyCardEvents]
      (
         [EmployeeID] INT NOT NULL,
         [AccessOperationDescription] NVARCHAR (1024) NOT NULL,
         [Timestamp] Datetime2 NOT NULL
      )
      WITH (LEDGER = ON (APPEND_ONLY = ON));
   

2. Add a new building access event in the [AccessControl].[KeyCardEvents] table with the following values.

   SQL

   INSERT INTO [AccessControl].[KeyCardEvents]
   VALUES ('43869', 'Building42', '2020-05-02T19:58:47.1234567');
   

3. View the contents of your KeyCardEvents table, and specify the GENERATED ALWAYS columns that are added to your append-only ledger table.

   SQL

   SELECT *
        ,[ledger_start_transaction_id]
        ,[ledger_start_sequence_number]
   FROM [AccessControl].[KeyCardEvents];
   

4. View the contents of your KeyCardEvents ledger view along with the ledger transactions system view to identify who added records into the table.

   SQL

    SELECT
    t.[commit_time] AS [CommitTime] 
    , t.[principal_name] AS [UserName]
    , l.[EmployeeID]
    , l.[AccessOperationDescription]
    , l.[Timestamp]
    , l.[ledger_operation_type_desc] AS Operation
    FROM [AccessControl].[KeyCardEvents_Ledger] l
    JOIN sys.database_ledger_transactions t
    ON t.transaction_id = l.ledger_transaction_id
    ORDER BY t.commit_time DESC;
   

5. Try to update the KeyCardEvents table by changing the EmployeeID from 43869 to 34184.

   SQL

   UPDATE [AccessControl].[KeyCardEvents] SET [EmployeeID] = 34184;
   

   You'll receive an error message that states the updates aren't allowed for your append-only ledger table.

   

Permissions

Creating append-only ledger tables requires the ENABLE LEDGER permission. For more information on permissions related to ledger tables, see Permissions.

Related content

• Append-only ledger tables
• How to migrate data from regular tables to ledger tables