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
-- !!!