#temporal tables

Temporal tables automatically track history. Query what data looked like at any point in time.Create Temporal TableCREATE TABLE Employees ( Id INT PRIMARY KEY, Name NVARCHAR(100), Salary DECIMAL(10,2), ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON);Query HistorySELECT *…

Optimizing SQL Server: Strategies to Minimize Logical Reads In today’s data-driven environment, optimizing database performance is crucial for maintaining efficient and responsive applications. One significant aspect of SQL Server optimization is reducing the number of logical reads. Logical reads refer to the process of retrieving data from the cache memory, and minimizing them can…

Temporal Tables–Simple Guide to implement History Tables using SQL Server 2016/2017–Part 4–Managing Temporal Tables and History Data

In previous posts, we saw

Part 1 – How to create Temporal Tables

Part 2 – Accessing History data from Temporal Tables

Part 3 – Modifying Temporal Tables, Disabling System versioning and why we need to disable ?

In this post we will see how to manage Temporal tables and History

To get list of Temporal Tables – Snippet 1

SELECT name, object_id, temporal_type, temporal_type_desc, history_table_id,…

Temporal Tables – Simple Guide to implement History Tables using SQL Server 2016/2017–Part 3–Modifying Temporal Tables, Disabling System Versioning and why we need to disable

Part 1 – How to create Temporal tables

Part 2 – Accessing History data from Temporal Tables

In this post we will how to modify Temporal Tables, How to disable System Versioning in Temporal tables to update large set data of without affecting history or truncate tables

Modifying Temporal Tables

For adding new columns to Temporal tables, Please use below command

ALTER TABLE dbo.Demo ADD DemoDesc…

Temporal Tables–Simple Guide to implement History Tables using SQL Server 2016/2017–Part 2–Accessing History data

In previous post, we saw how to create Temporal tables or enable System Versioning on an existing table

In this post, we will review how to access History data

Using below code, we will create simple Temporal table and inserted 5 records and querying both the Main and History table

CREATE TABLE dbo.Demo ( DemoID int NOT NULL PRIMARY KEY CLUSTERED IDENTITY , DemoName varchar(50) NOT NULL ,…