Ledger - SQL Server

In SQL Server 2022, the Ledger feature is a new addition that provides a built-in way to track and audit changes to data in a database. The Ledger feature maintains a complete history of all changes made to specified tables, including inserts, updates, and deletes, and allows you to easily view and analyze this history to track changes and identify issues.

To use the Ledger feature, you first need to enable it on a database by setting the "IS_LEADGER_ENABLED" database configuration option to "ON". Once this is done, you can then specify which tables you want to track using the "ALTER TABLE" command with the "WITH (SYSTEM_VERSIONING = ON)" option.

For example, the following command enables the Ledger feature for a table named "Customer" in a database named "Sales":

ALTER TABLE [Sales].[dbo].[Customer] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CustomerHistory]));

This will enable the Ledger feature for the "Customer" table and create a new history table named "CustomerHistory" to store the history of all changes to the table.

Once the Ledger feature is enabled for a table, you can use the "FOR SYSTEM_TIME" clause in queries to view the history of changes to the table. For example, the following query retrieves all rows from the "Customer" table as they existed on a specific date:

SELECT * FROM [Sales].[dbo].[Customer] FOR SYSTEM_TIME AS OF '2022-02-15 00:00:00.000';

This will return all rows from the "Customer" table as they existed on February 15th, 2022, based on the history tracked by the Ledger feature.

You can also use the "CHANGES" function to retrieve a list of changes made to a table over a specific period of time. For example, the following query retrieves all changes made to the "Customer" table in the past day:

SELECT * FROM [Sales].[dbo].[Customer] FOR SYSTEM_TIME FROM '2022-02-14 00:00:00.000' TO '2022-02-15 00:00:00.000' WHERE CHANGES() IS NOT NULL;

This will return all rows from the "Customer" table that were changed between February 14th and February 15th, 2022, based on the history tracked by the Ledger feature.

The Ledger feature in SQL Server 2022 provides a powerful way to track and audit changes to data in a database, and can be useful for compliance, auditing, and troubleshooting purposes. By enabling the Ledger feature and using the appropriate queries, you can easily view and analyze the history of changes to tables in a database and track data changes over time.

Note - Because the ledger feature cannot be turned off, you must plan your storage requirements accordingly.




Comments

Post a Comment

Popular posts from this blog

COPILOT Feature in SQL Server 2025

Image
 Copilot Feature in SQL Server 2025: A Game-Changer for Database Management As organizations continue to embrace digital transformation, the demand for smarter, more efficient database management tools is growing. Enter SQL Server 2025, Microsoft's latest iteration of its flagship database system, which introduces a revolutionary feature—Copilot. Powered by advanced AI capabilities, Copilot is set to redefine how database administrators (DBAs) and developers interact with SQL Server. What is Copilot in SQL Server 2025? Copilot is an AI-powered assistant integrated into SQL Server 2025. Designed to augment productivity, it leverages natural language processing (NLP) and machine learning (ML) to assist users in managing databases, writing queries, optimizing performance, and ensuring data security. Built on Microsoft's Azure OpenAI service, Copilot brings intelligence directly into the SQL Server ecosystem. Key Features of Copilot Natural Language Querying : Users can write quer...
Read more

Prefetching - SQL Server

Prefetching is a technique used by SQL Server to improve query performance by anticipating the data that will be needed for a query and reading it into memory in advance. This reduces the number of I/O operations required to fetch data from disk, which can significantly improve query performance. There are several mechanisms that SQL Server uses for prefetching, including: Read-Ahead : This mechanism anticipates the data pages that will be needed for a query and reads them from disk into memory in advance. This is based on a predictive algorithm that uses information from the query execution plan to determine which pages are likely to be needed. Pre-Fetching : This mechanism anticipates the related data that will be needed for a query and reads it from related tables into memory in advance. This is based on the relationships defined in the query and can significantly reduce the number of I/O operations required during query execution. Parallelism : SQL Server can use multiple threads ...
Read more

Split Brain - SQL Server

Split Brain is a term used in SQL Server clustering to describe a scenario where the nodes in the cluster lose communication with each other, and each node believes that it is the only active node in the cluster. This can cause multiple issues, including data corruption, duplicate transactions, and other unexpected behaviors. Here's an in-depth detail of split brain in SQL Server clustering with examples: In a SQL Server clustering environment, there are multiple nodes that are connected to each other via a network. Each node has access to the same shared storage, which contains the SQL Server database files. In a normal situation, the nodes communicate with each other to ensure that only one node is active at any given time. This is achieved through a mechanism called a cluster heartbeat, where the nodes send signals to each other to indicate that they are still alive and functioning. However, in certain scenarios, the cluster heartbeat may fail, and the nodes may lose communicat...
Read more