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-adminplugin (mssql_list_database_connections,mssql_connect_database, thenmssql_execute_query); any other SQL tool the agent host provides for running T-SQL and reading result sets;sqlcmdfrom 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 thesql-tools-mcp-setupskill, 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
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
SELECT name AS LedgerViewName
FROM sys.views
WHERE object_id = '$LedgerViewID'
OPTION (MAXDOP 1);
List All Ledger Tables
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:
- Check if the table is a ledger table
- Use the system-created ledger view (not the history table directly)
- Get the view's
object_idfromsys.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
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
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.