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

Question

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 ('[email protected]', 1);
GO

INSERT [dbo].[Person] VALUES ('[email protected]', 1);
GO

INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('[email protected]', 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] = '[email protected]' WHERE [EmailAddress] = '[email protected]';
GO
UPDATE [dbo].[Person] SET [EmailAddress] = '[email protected]' WHERE [EmailAddress] = '[email protected]';
GO
--Update Rows (Dummy Update)
UPDATE [dbo].[Person] SET [EmailAddress] = '[email protected]' WHERE [EmailAddress] = '[email protected]';
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           [email protected]                                                         1            2017-03-14 16:12:29.5018193 2017-03-14 16:13:27.0374068
-- 2           [email protected]                                                         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.

Answer 1

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

Answer 2

We use an old technique where each record has a 'Checksum'. This checksum is calculated in a trigger whenever a record is created or updated.We compare the checksums on the records and if they are different then we update the record.

Something like this...

UPDATE PRODUCTIONTABLE SET EMAILADDRESS = STAGINGTABLE.EMAILADDRESS WHERE PRODUCTIONTABLE.UNIQUEKEY = STAGINGTABLE.UNIQUEKEY AND PRODUCTIONTABLE.CHECKSUM <> STAGINGTABLE.CHECKSUM