4

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](
    [PersonID] [int] NOT NULL,
    [EmailAddress] [varchar](75) 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](
    [PersonID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [EmailAddress] [varchar](75) 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 IN (1,2,3);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:13:27.0374068

--Update Rows (Actual Updates)
UPDATE [dbo].[Person] SET [EmailAddress] = 'SD.Burman@hotmail.com' WHERE [EmailAddress] = 'SD.Burman@gmail.com';
GO
UPDATE [dbo].[Person] SET [EmailAddress] = 'RD.Burman@hotmail.com' WHERE [EmailAddress] = 'RD.Burman@gmail.com';
GO
--Update Rows (Dummy Update)
UPDATE [dbo].[Person] SET [EmailAddress] = 'AR.Rehman@gmail.com' WHERE [EmailAddress] = 'AR.Rehman@gmail.com';
GO

-- Query the history table
SELECT *
FROM dbo.PersonHistory

-- !!!
-- What I'd want is that the Id = 3 update to not get saved since the data did not change.
-- Also a way I can limit the number of columns I want this behavior on.
-- PersonID    EmailAddress                                                                IsSubscribed SysStartTime                SysEndTime
-- ----------- --------------------------------------------------------------------------- ------------ --------------------------- ---------------------------
-- 1           SD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5018193 2017-03-14 16:13:27.0374068
-- 2           RD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5118172 2017-03-14 16:13:27.1484051
-- !!!

Similar question highlighting this behavior:

Do temporal tables log changes when there are none?

I'm looking for a workaround for the mentioned behavior.

Change Data Capture would be able to handle this (or even custom triggers for that matter), but I'm exploring if I could get the benefits of temporal tables with selective history recording.

The challenge is, we have an ETL job that touches the syncdatatime columns often, and we want to exclude or include certain columns, as well only record when the actual data value changes, preferably with the temporal tables.

  • The scenario I have is a rowversion column being the only change logged in the history table. I have a legacy system that executes updates on tables with rowversion columns without checking if data in any other column has changed. A feature to declaritively "ignore a specified column" in determining whether to write a row to the history table would solve my issue where updates with no real data changes are generating history rows. – John Mo Aug 3 '18 at 12:44
2

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:

  1. Stretch Database
  2. Table Partitioning
  3. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.