# Ledger Tables

> Use when the user asks about ledger tables: whether ledger fits a tamper-evidence need, creating updatable or append-only ones, or listing them and their history. SQL Server 2022+ and Azure SQL Database.

- Skill: `microsoft/ledger-tables` (Agent Skill)
- Install (CLI): `npx get-skill add microsoft/ledger-tables`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/ledger-tables/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/ledger-tables

---


# Ledger Tables

Use this skill when the user asks about ledger tables, creating ledger tables, querying historical ledger data, or auditing data changes.

## Required tools

- A SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP
  server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,
  `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides
  for running T-SQL and reading result sets;
  [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. The same
  tool also creates ledger tables when the user asks you to make the change. If the SQL Tools MCP
  server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill, which
  asks before installing or configuring anything. If no SQL tool can be made available, give the
  user the T-SQL to run and ask for the results.

## SQL Queries

### GetHistoryTableForUpdatableLedgerTable
```sql
SELECT name FROM sys.tables
WHERE ledger_type = 1
  AND object_id = (SELECT history_table_id FROM sys.tables WHERE name = '$LedgerTableName' AND ledger_type = 2)
  OPTION (MAXDOP 1);
```

### GetSystemLedgerViewForLedgerTable
```sql
SELECT name AS LedgerViewName
FROM sys.views
WHERE object_id = '$LedgerViewID'
OPTION (MAXDOP 1);
```

### List All Ledger Tables
```sql
SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName,
    ledger_type_desc AS LedgerType,
    ledger_view_id
FROM sys.tables
WHERE ledger_type IN (2, 3)
  AND is_dropped_ledger_table = 0
ORDER BY SchemaName, TableName
OPTION (MAXDOP 1);
```

## Ledger Table Types

### Updatable Ledger Tables
- Use `SYSTEM_VERSIONING = ON` (temporal tables) to maintain full history of changes.
- Ledger type = 2 in `sys.tables`.

### Append-Only Ledger Tables
- Only allow inserts — no updates or deletes.
- Ledger type = 3 in `sys.tables`.

### Unsupported Data Types
XML, SqlVariant, User-defined, FILESTREAM, Vector

## Querying Historical Data

When asked about historical data for a table:
1. Check if the table is a ledger table
2. Use the **system-created ledger view** (not the history table directly)
3. Get the view's `object_id` from `sys.tables.ledger_view_id`

**DO NOT** use the history table directly unless specifically asked.
**DO NOT** use undocumented functions like `DBCC log`, `fn_dblog()`, `fn_dump_dblog()`, `fn_full_dblog()`.

### System-Created Ledger View Columns

| Column | Type | Description |
|--------|------|-------------|
| ledger_transaction_id | BIGINT | Transaction ID that created/deleted a row |
| ledger_sequence_number | BIGINT | Sequence number of row operation |
| ledger_operation_type | TINYINT | 1 = INSERT, 2 = DELETE |
| ledger_operation_type_desc | NVARCHAR(128) | INSERT or DELETE |

### Metadata in sys.tables

| Column | Values |
|--------|--------|
| ledger_type | 0=Non-ledger, 1=History, 2=Updatable, 3=Append-only |
| ledger_view_id | object_id of the ledger view (for types 2 and 3) |
| is_dropped_ledger_table | bit — dropped but preserved for auditing |

**DO NOT** include dropped ledger tables or history tables when listing ledger tables unless specifically asked.

## Creating Ledger Tables

### Updatable Ledger Table Template
```sql
CREATE TABLE [schema].[table_name]
(
    column1 datatype,
    ledger_start_transaction_id BIGINT GENERATED ALWAYS AS TRANSACTION_ID START,
    ledger_end_transaction_id BIGINT GENERATED ALWAYS AS TRANSACTION_ID END,
    ledger_start_sequence_number BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER START,
    ledger_end_sequence_number BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER END
)
WITH
(
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [schema].[history_table_name]),
    LEDGER = ON (
        LEDGER_VIEW = [schema].[view_name] (
            TRANSACTION_ID_COLUMN_NAME = ledger_transaction_id,
            SEQUENCE_NUMBER_COLUMN_NAME = ledger_sequence_number,
            OPERATION_TYPE_COLUMN_NAME = ledger_operation_type,
            OPERATION_TYPE_DESC_COLUMN_NAME = ledger_operation_type_desc
        )
    )
);
```

### Append-Only Ledger Table Template
```sql
CREATE TABLE [schema].[table_name]
(
    column1 datatype,
    ledger_start_transaction_id BIGINT GENERATED ALWAYS AS TRANSACTION_ID START,
    ledger_start_sequence_number BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER START
)
WITH
(
    LEDGER = ON (
        APPEND_ONLY = ON,
        LEDGER_VIEW = [schema].[view_name] (
            TRANSACTION_ID_COLUMN_NAME = ledger_transaction_id,
            SEQUENCE_NUMBER_COLUMN_NAME = ledger_sequence_number,
            OPERATION_TYPE_COLUMN_NAME = ledger_operation_type,
            OPERATION_TYPE_DESC_COLUMN_NAME = ledger_operation_type_desc
        )
    )
);
```

If the user does not specify updatable vs. append-only, ask for clarification before creating.

