patternsqlMinor
Change the behavior of temporal tables to log actual value changes rather than dummy updates
Viewed 0 times
temporaltablesthedummyactuallogthanbehaviorvaluerather
Problem
Currently the articles I read on temporal tables push the responsibility of the clean-up to the application code. Is it possible to configure the temporal tables to record particular column value changes instead of when the update statement is called on the table?
Example for the expected behavior:
```
USE Master;
GO
-- Create test database
CREATE DATABASE [TemporalTables];
GO
USE TemporalTables;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Hidden period columns
CREATE TABLE [dbo].PersonHistory NULL,
[IsSubscribed] [bit] NULL,
[SysStartTime] datetime2 NOT NULL,
[SysEndTime] datetime2 NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX IX_PersonHistory
ON PersonHistory;
CREATE NONCLUSTERED INDEX IX_PersonHistory_ID_PERIOD_COLUMNS
ON PersonHistory (SysEndTime, SysStartTime, PersonID);
GO
CREATE TABLE [dbo].Person NOT NULL PRIMARY KEY CLUSTERED,
[EmailAddress] varchar NULL,
[IsSubscribed] [bit] NULL,
[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
[SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)
);
-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:11:56.6294004
--Insert Rows
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed]) VALUES ('SD.Burman@gmail.com', 1);
GO
INSERT [dbo].[Person] VALUES ('RD.Burman@gmail.com', 1);
GO
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('AR.Rehman@gmail.com', 1, default, default);
GO
-- Query the main table
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
-- Shows all changes for specific PK (for inserts to show up)
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
FOR SYSTEM_TIME ALL
where Personid
Example for the expected behavior:
```
USE Master;
GO
-- Create test database
CREATE DATABASE [TemporalTables];
GO
USE TemporalTables;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Hidden period columns
CREATE TABLE [dbo].PersonHistory NULL,
[IsSubscribed] [bit] NULL,
[SysStartTime] datetime2 NOT NULL,
[SysEndTime] datetime2 NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX IX_PersonHistory
ON PersonHistory;
CREATE NONCLUSTERED INDEX IX_PersonHistory_ID_PERIOD_COLUMNS
ON PersonHistory (SysEndTime, SysStartTime, PersonID);
GO
CREATE TABLE [dbo].Person NOT NULL PRIMARY KEY CLUSTERED,
[EmailAddress] varchar NULL,
[IsSubscribed] [bit] NULL,
[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
[SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)
);
-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:11:56.6294004
--Insert Rows
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed]) VALUES ('SD.Burman@gmail.com', 1);
GO
INSERT [dbo].[Person] VALUES ('RD.Burman@gmail.com', 1);
GO
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('AR.Rehman@gmail.com', 1, default, default);
GO
-- Query the main table
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
-- Shows all changes for specific PK (for inserts to show up)
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
FOR SYSTEM_TIME ALL
where Personid
Solution
On a further dive it seems that this currently falls under : Manage Retention of Historical Data in System-Versioned Temporal Tables as a reactionary solution to the problem, instead of a prior configuration.
And they propose three approaches:
There are some other nice articles I found, which partially answer my question, however, I'd love if I can manage this behavior at the point of data creation rather than the cleanup, so I'd request more information in case someone has it, or if this changes in the future.
Other articles:
Managing Temporal Table History in SQL Server 2016
Temporal Table Considerations and Limitations
And they propose three approaches:
- Stretch Database
- Table Partitioning
- Custom Cleanup Script
There are some other nice articles I found, which partially answer my question, however, I'd love if I can manage this behavior at the point of data creation rather than the cleanup, so I'd request more information in case someone has it, or if this changes in the future.
Other articles:
Managing Temporal Table History in SQL Server 2016
Temporal Table Considerations and Limitations
Context
StackExchange Database Administrators Q#167136, answer score: 2
Revisions (0)
No revisions yet.