Append Only Ledger Table In SQL Server

Introduction

In the last article SQL Server Ledger, I explained all about SQL Server Ledger and Updatable Ledger Tables. Before moving further in this article, It is highly recommended that you read that article first. An append-only ledger table is a type of database table that only allows new data to be appended to the end of the table. Once data is added to an append-only ledger table, it cannot be changed or deleted. This makes append-only ledger tables ideal for applications that require a tamper-proof record of events, such as security information and event management (SIEM) systems, blockchain systems, and financial transaction systems.

Append Only Ledger Table

Append-only ledger tables are created with the LEDGER = ON (APPEND_ONLY = ON) option in the CREATE TABLE statement. This option prevents users from performing UPDATE or DELETE operations on the table. Instead, any changes to the data in an append-only ledger table must be made by creating a new row in the table.

ledger table architecture append only

Append-only ledger tables are a powerful tool for ensuring the integrity of data. By preventing users from changing or deleting data, append-only ledger tables can help to protect against fraud, tampering, and other malicious activities.

The syntax to create an append-only ledger table is given below.

CREATE TABLE [Transaction]
(
    Id BIGINT IDENTITY(1,1) Primary Key NOT NULL,
	FromUserId BIGINT NOT NULL,
	ToUserId BIGINT NOT NULL,
	Amount NUMERIC(15,2) NOT NULL,
	CreatedDate DATETIME NOT NULL DEFAULT GETUTCDATE(),
)
WITH (
   LEDGER = ON (
       APPEND_ONLY = ON
        )
)
GO

Output

append only ledger table structure

As we can see in the above image, the append-only ledger table creates one more table apart from the main table as given below

Transaction (The main table)
Transaction_Ledger (The View)

Append-Only ledger table does not create a history table like an updatable ledger table because it does not allow us to update or delete anything from the table. The main table also contains two more default columns, ledger_start_transaction_id and ledger_start_sequence_number.

Now let's do some operations on the table.

INSERT

Insert some data in the table as given below

INSERT INTO [Transaction](FromUserId, ToUserId, Amount)
                   VALUES(101,110,2500),
                         (101,120,1000),
                         (101,130,2000)

Now execute the below query to see the data in all the tables

    --main table
	 select [Id], [FromUserId],[ToUserId], [Amount], [CreatedDate],
     [ledger_start_transaction_id],
     [ledger_start_sequence_number] from [Transaction]

	 --view table
	select * from Transaction_Ledger
	order by ledger_transaction_id,ledger_sequence_number

Output

insert operation output append-only table

As you can see in the above image, we have simply inserted data in the tables.

UPDATE

Now let's execute the update query on the append-only ledger table as given below.

UPDATE [Transaction] SET Amount=2500 WHERE Id=2

Output

Update append only table ouput

As you can see in the above image, clearly, you cannot update data in the append-only ledger table.

DELETE

Now let's execute the delete query and see what will happen.

DELETE FROM [Transaction] WHERE Id=2

Output

delete append only ledger table

As you can see, you cannot delete data from the append-only ledger table.

Here are some of the benefits of using append-only ledger tables.

Tamper-proof record of events: Append-only ledger tables cannot be modified after data is added, which makes them ideal for applications that require a tamper-proof record of events.
Increased security: Append-only ledger tables can help to protect against fraud and tampering by preventing users from changing or deleting data.
Improved auditing: Append-only ledger tables can make it easier to audit data changes by providing a complete history of all changes that have been made to the table.
Reduced data storage costs: Append-only ledger tables only need to store the latest version of each record, which can help to reduce data storage costs.

Here are some of the drawbacks of using append-only ledger tables.

Limited data manipulation: Append-only ledger tables only allow INSERT operations, which can limit the types of data analysis that can be performed.
Increased complexity: Append-only ledger tables can be more complex to implement and manage than traditional database tables.
Reduced performance: Append-only ledger tables can have a negative impact on performance, especially for applications that require frequent data updates.

Benefits of using SQL Server Ledger

There are several benefits to using SQL Server Ledger, including:

Tamper-evident: SQL Server Ledger provides tamper-evident capabilities for your database. This means that you can cryptographically attest to other parties that your data hasn't been tampered with.
Auditability: SQL Server Ledger makes it easy to audit your database. The history of changes to your database is stored in a history table, which allows you to see the original value of a row, as well as the changes that have been made to the row over time.
Compliance: SQL Server Ledger can help you to comply with regulations that require you to maintain the integrity of your data.

Conclusion

Overall, append-only ledger tables are a powerful tool for ensuring the integrity of data. However, they should be used with caution in applications where performance is critical.

To learn more about how to use SQL Server Ledger, you can refer to the following resources:

SQL Server Ledger Overview: Ledger Overview
Create and use append-only ledger tables: Append-Only Ledger Table

I hope this article has helped you to understand what SQL Server Ledger is and how it works. If you have any other questions, please feel free to ask me.

Thank You, and Stay Tuned for More