{"id":6417,"date":"2026-02-09T09:34:56","date_gmt":"2026-02-09T17:34:56","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=6417"},"modified":"2026-02-23T11:16:34","modified_gmt":"2026-02-23T19:16:34","slug":"time-travel-in-azure-sql-with-temporal-tables","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/time-travel-in-azure-sql-with-temporal-tables\/","title":{"rendered":"Time Travel in Azure SQL with Temporal Tables"},"content":{"rendered":"

Applications often need to know what data looked like before. Who changed it, when it changed, and what the previous values were. Rebuilding that history in application code is tedious and error prone. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where information discovery matters.<\/p>\n

\n

Learn more about SQL MCP Server at https:\/\/aka.ms\/sql\/mcp<\/a><\/p>\n<\/blockquote>\n

Azure SQL includes a built in feature that tracks row history automatically. Temporal tables let the database keep a full change history without triggers, audit tables, or custom logic.<\/p>\n

\n

Working demo https:\/\/gist.github.com<\/a><\/p>\n<\/blockquote>\n

What We Are Building<\/h2>\n

A table that automatically tracks every change over time.<\/p>\n

Step 1: Create the Table<\/h2>\n

A temporal table requires two datetime columns that define the validity period. Azure SQL manages these automatically when you enable system versioning.<\/p>\n

CREATE TABLE dbo.Todos\n(\n    Id        INT IDENTITY(1,1) PRIMARY KEY,\n    Title     NVARCHAR(200) NOT NULL,\n    State     NVARCHAR(20)  NOT NULL DEFAULT 'pending',\n\n    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,\n    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END,\n    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)\n)\nWITH (\n    SYSTEM_VERSIONING = ON\n    (\n        HISTORY_TABLE = dbo.TodosHistory\n    )\n);\nGO\n\nINSERT INTO dbo.Todos (Title, State) VALUES\n    ('Buy groceries', 'pending'),\n    ('Walk the dog',  'pending');\nGO\n<\/code><\/pre>\n
This table is created as a system-versioned temporal table. The ValidFrom<\/code> and ValidTo<\/code> columns define the period during which each row version is valid, and Azure SQL maintains them automatically. With SYSTEM_VERSIONING = ON<\/code>, every UPDATE and DELETE causes the previous version of the row to be written to the history table. By specifying HISTORY_TABLE = dbo.TodosHistory<\/code>, you control the name and schema of the history table, while Azure SQL continues to manage its contents and lifecycle. If you do not provide a history table, Azure SQL creates one for you automatically.<\/p>\n
Step 2: Update Data Normally<\/h2>\n
No special syntax is required. Changes are tracked automatically.<\/p>\n
UPDATE dbo.Todos\nSET State = 'completed'\nWHERE Title = 'Walk the dog';\nGO\n<\/code><\/pre>\n
Each update creates a historical version of the row.<\/p>\n
Step 3: Query Current Data<\/h2>\n
SELECT * FROM dbo.Todos;\n<\/code><\/pre>\n
Result<\/h4>\n\n\n\n\n\n\n
Id<\/th>\nTitle<\/th>\nState<\/th>\nValidFrom<\/th>\nValidTo<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nBuy groceries<\/td>\npending<\/td>\ncurrent<\/td>\n9999-12-31<\/td>\n<\/tr>\n
2<\/td>\nWalk the dog<\/td>\ncompleted<\/td>\ncurrent<\/td>\n9999-12-31<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Step 4: Query Historical Data<\/h2>\n
Use FOR SYSTEM_TIME<\/code> to query past versions.<\/p>\n
All Versions<\/h3>\n
SELECT *\nFROM dbo.Todos\nFOR SYSTEM_TIME ALL\nORDER BY Id, ValidFrom;\n<\/code><\/pre>\n
Result<\/h4>\n\n\n\n\n\n\n
Id<\/th>\nTitle<\/th>\nState<\/th>\nValidFrom<\/th>\nValidTo<\/th>\n<\/tr>\n<\/thead>\n
2<\/td>\nWalk the dog<\/td>\npending<\/td>\nearlier<\/td>\nupdate time<\/td>\n<\/tr>\n
2<\/td>\nWalk the dog<\/td>\ncompleted<\/td>\nupdate time<\/td>\n9999-12-31<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As Of a Point in Time<\/h3>\n
DECLARE @time DATETIME2 = DATEADD(MINUTE, -1, SYSUTCDATETIME());\n\nSELECT *\nFROM dbo.Todos\nFOR SYSTEM_TIME AS OF @time;\n<\/code><\/pre>\n
This returns the table exactly as it existed at that moment.<\/p>\n
Why Use Temporal Tables?<\/h2>\n
    \n
  • History is enforced by the database<\/li>\n
  • No triggers or audit tables to maintain<\/li>\n
  • All updates and deletes are captured<\/li>\n
  • Application code stays simple<\/li>\n
  • Works automatically with backups and restores<\/li>\n
  • Allows reversible operations<\/li>\n
  • Gives AI and automation traceability<\/li>\n<\/ul>\n
    Temporal tables move data history into the database engine itself. Once enabled, every change becomes observable, queryable, and reliable without extra code.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Applications often need to know what data looked like before. Who changed it, when it changed, and what the previous values were. Rebuilding that history in application code is tedious and error prone. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where information discovery […]<\/p>\n","protected":false},"author":96788,"featured_media":6604,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,597,710,619],"tags":[560,711,34],"class_list":["post-6417","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-data-api-builder-2","category-sql-mcp-server","category-t-sql","tag-data-api-builder","tag-sql-mcp-server","tag-t-sql"],"acf":[],"blog_post_summary":"
    Applications often need to know what data looked like before. Who changed it, when it changed, and what the previous values were. Rebuilding that history in application code is tedious and error prone. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where information discovery […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=6417"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/6417\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/6604"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=6417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=6417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=6417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}