Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Temporal Tables: Track Data Changes Over Time

- 29.08.26 - ErcanOPAK

๐Ÿ“… Key Insights: Temporal tables automatically track history of data changes. Perfect for auditing, compliance, and time-travel queries.

Need to know what data looked like last month? Temporal tables make it easy.

๐Ÿš€ Create Temporal Table

-- โœ… Create table with temporal support
CREATE TABLE Orders
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status NVARCHAR(50) NOT NULL,
    -- โœ… System columns
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

๐Ÿ” Query Temporal Data

-- โœ… Current data
SELECT * FROM Orders WHERE Id = 123;

-- โœ… Data as of specific time
SELECT * FROM Orders
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00'
WHERE Id = 123;

-- โœ… Data between two times
SELECT * FROM Orders
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-01-31'
WHERE CustomerId = 456;

-- โœ… All changes (including history)
SELECT * FROM Orders
FOR SYSTEM_TIME ALL
WHERE CustomerId = 456
ORDER BY SysStartTime;

-- โœ… Last change before specific time
SELECT TOP 1 *
FROM Orders
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00'
WHERE CustomerId = 456
ORDER BY SysStartTime DESC;

-- โœ… Audit trail for specific order
SELECT 
    Id,
    Status,
    SysStartTime AS ChangedAt,
    LAG(Status) OVER (ORDER BY SysStartTime) AS PreviousStatus
FROM Orders
FOR SYSTEM_TIME ALL
WHERE Id = 123
ORDER BY SysStartTime;

๐Ÿ’ก Pro Tip: Clean Up History

-- โœ… Manage history table
ALTER TABLE Orders SET (SYSTEM_VERSIONING = OFF);

-- โœ… Clean old history
DELETE FROM OrdersHistory
WHERE SysEndTime < DATEADD(YEAR, -1, GETUTCDATE());

-- โœ… Re-enable system versioning
ALTER TABLE Orders 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

Related posts:

SQL โ€œSlow LIKE Queryโ€ โ€” The Real Fix (Not Indexes!)

SQL โ€œSlow LIKE Queryโ€ โ€” The Real Fix (Not Indexes!)

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

DATEDIFF in WHERE Clause Disables Indexes

DATEDIFF in WHERE Clause Disables Indexes

Views: 11

Post navigation

ASP.NET Core Authentication & Authorization: Deep Dive into Security
Step-by-Step: Kestrel Web Server – High-Performance ASP.NET Core Hosting

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com