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.

Create an append-only ledger table

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

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

   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.

   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.

   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.

    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.

   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