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