Temporal History Queries: Indexing the History Table for Speed

Temporal history queries often look at a table you never think about: the history table. Your current table can be tiny and fast while every “as of” question quietly searches millions of old versions. Before you add an index to fix it, measure the lookup and count the cost.

Where the old rows live

Picture an auditor asking, “What was the price of item 1500 at two o’clock last Tuesday?” You run an AS OF query. It comes back fast on your laptop. In production it can take seconds, and the current table has only a few thousand rows. What gives?

An AS OF query reads the current table and the history table, then combines them. Every UPDATE or DELETE moves the old row into history. That table grows for as long as you keep versions. By default, SQL Server gives it one clustered index, ordered by the period columns, ValidTo then ValidFrom. That order is great for “show me everything in this time window.” It is not great for “show me this one item.”

Build a small temporal table

The demo makes a price table with system versioning on and an explicit history table name. Naming it yourself makes the history table easy to find. The demo creates both tables and removes them at the end.

IF OBJECT_ID(N'dbo.ItemPrice', N'U') IS NOT NULL
BEGIN
    ALTER TABLE dbo.ItemPrice SET (SYSTEM_VERSIONING = OFF);
    DROP TABLE dbo.ItemPrice;
END;
DROP TABLE IF EXISTS dbo.ItemPriceHistory;
GO
CREATE TABLE dbo.ItemPrice (
    ItemId int NOT NULL PRIMARY KEY,
    Amount decimal(12,2) NOT NULL,
    ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ItemPriceHistory));

Now load 2000 items at a price of 10. Save the moment right after the load, then raise every price twice. Each update pushes the old rows into history.

INSERT dbo.ItemPrice (ItemId, Amount)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 10
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

SELECT MAX(ValidFrom) AS AsOfPoint INTO #AsOfMoment FROM dbo.ItemPrice;

WAITFOR DELAY '00:00:00.100';
UPDATE dbo.ItemPrice SET Amount = 20;
WAITFOR DELAY '00:00:00.100';
UPDATE dbo.ItemPrice SET Amount = 30;

SELECT (SELECT COUNT(*) FROM dbo.ItemPrice) AS current_rows,
       (SELECT COUNT(*) FROM dbo.ItemPriceHistory) AS history_rows;

The counts show 2000 current rows and 4000 history rows. The history table is already twice the size of the table you query. Give it a year of real traffic and the gap becomes huge.

Ask the question before any index

Now ask for item 1500 as of the saved moment. STATISTICS IO shows which tables the query touched.

DECLARE @AsOf datetime2(7) = (SELECT AsOfPoint FROM #AsOfMoment);

SET STATISTICS IO ON;
SELECT ItemId, Amount
FROM dbo.ItemPrice FOR SYSTEM_TIME AS OF @AsOf
WHERE ItemId = 1500;
SET STATISTICS IO OFF;

The answer is 10.00, the price before either update. The messages tab shows both tables. ItemPrice costs 2 logical reads. ItemPriceHistory costs 8, because SQL Server has to scan through the history to find that one item.

Add an index that starts with the key

Item lookups need ItemId first. The index below puts ItemId first, then ValidTo and ValidFrom, and carries Amount along so the query never visits the base rows. Then the same lookup runs again.

CREATE INDEX IX_ItemPriceHistory_ItemPeriod
ON dbo.ItemPriceHistory (ItemId, ValidTo, ValidFrom) INCLUDE (Amount);
GO
DECLARE @AsOf datetime2(7) = (SELECT AsOfPoint FROM #AsOfMoment);

SET STATISTICS IO ON;
SELECT ItemId, Amount
FROM dbo.ItemPrice FOR SYSTEM_TIME AS OF @AsOf
WHERE ItemId = 1500;
SET STATISTICS IO OFF;

Same answer, 10.00. The history table now costs 2 logical reads instead of 8. That is a win, but a small one, because this history table is tiny. On a big one, the scan keeps growing while the seek stays about the same. I did not test a big one here, so measure yours before you promise anyone a number.

Check what the index costs you

Every retained version now lives in two places. Each UPDATE has to write the old row into both. This query shows both structures with their row counts and sizes.

SELECT i.name AS index_name, i.type_desc, ps.row_count, ps.used_page_count
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS ps
  ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.ItemPriceHistory')
ORDER BY i.index_id;

Both indexes hold 4000 rows. In my run the new one also took about three times as many pages as the history table’s own index. That is the write bill. Test your update workload as well as your reads, and keep the query shapes with the decision. A broad report over a time window may prefer the original order.

The last block removes the demo. Turning versioning off is fine here because the tables are throwaways. Never do that on real history just to make an experiment easier.

ALTER TABLE dbo.ItemPrice SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.ItemPrice, dbo.ItemPriceHistory;
DROP TABLE #AsOfMoment;

Run the AS OF query on your own history table first, and look at the reads before you add anything.

A history index is not free speed, it is another structure maintained with each retained version.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.