HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Change the behavior of temporal tables to log actual value changes rather than dummy updates

Submitted by: @import:stackexchange-dba··
0
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

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:

  • 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.