SQL Server Temporal Tables – lathund

By Johan Åhlén | december 14, 2016

0 Comment

Letar du efter en enkel ”så-gör-du” sammanfattning för temporal tables, inklusive aktuell T-SQL syntax? Här är den. Om du behöver en längre förklaring så rekommenderar jag mina artiklar på SQL Server Central, Microsoft MVP blog eller MSDN dokumentationen.

Skapa temporal tables

CREATE TABLE ...
(
...
SysStartTime DATETIME2(0) GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2(0) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...));

Notes: You can choose any name for the columns. You can also choose any DATETIME2 precision you want. You don’t need to specify the name of the history table.

Gör en vanlig tabell till temporal

ALTER TABLE ... ADD
	SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Tablename_SysStartTime DEFAULT '1900-01-01 00:00:00',
	SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Tablename_SysEndTime DEFAULT '9999-12-31 23:59:59',
	PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

ALTER TABLE ...
	SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...));

Notes: You can choose any name for the columns. You can also choose any DATETIME2 precision you want. You don’t need to specify the name of the history table. The default value for SysStartTime could be any time in the past. The default value for SysEndTime must be the maximum DATETIME2 value.

Gör en temporal table till en vanlig

ALTER TABLE [dbo].[Inventory] SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE [dbo].[Inventory] DROP PERIOD FOR SYSTEM_TIME;
ALTER TABLE [dbo].[Inventory] DROP COLUMN [SysStartTime], [SysEndTime];

Notes: If you have default-constraints on the period columns, you will have to delete the default-constraints first.

Hantera längden på historik i SQL Azure temporal tables

ALTER DATABASE CURRENT
SET TEMPORAL_HISTORY_RETENTION ON

GO

ALTER TABLE [dbo].[Inventory]
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = ... DAYS));

Point-in-time queries

SELECT ...
FROM ...
FOR SYSTEM_TIME AS OF ...

Interval queries

SELECT ...
FROM ...
FOR SYSTEM_TIME BETWEEN ... AND

SELECT ...
FROM ...
FOR SYSTEM_TIME FROM ... TO

SELECT ...
FROM ...
FOR SYSTEM_TIME CONTAINED IN (..., ...)

Notes: The difference between the three interval queries is explained here.

Hämta all nuvarande och historisk data

SELECT ...
FROM ...
FOR SYSTEM_TIME ALL

Referenser

Related Posts