Temporals and Partitioning

/*Drop to start fresh*/
ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS dbo.Votes_Deletes_History;
DROP TABLE IF EXISTS dbo.Votes_Deletes;
DROP TABLE IF EXISTS dbo.Votes_Deletes_Stage;

/*Create a partition function by year*/
CREATE PARTITION FUNCTION pf_VotesDeletes (DATETIME)
    AS RANGE RIGHT FOR VALUES('20070101', '20080101', '20090101', '20100101', '20110101', '20120101', '20130101', '20140101')

/*Create a partition scheme*/
CREATE PARTITION SCHEME ps_VotesDeletes 
	AS PARTITION pf_VotesDeletes
	ALL TO ([PRIMARY]);

/*Create a table with temporalness*/
CREATE TABLE dbo.Votes_Deletes
(
    Id INT NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    SysStartTime DATETIME2(7) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT df_VotesDeletes_SysStartTime
            DEFAULT SYSDATETIME(),
    SysEndTime DATETIME2(7) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT df_VotesDeletes_SysEndTime
            DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')
        CONSTRAINT dr_rockzo
        PRIMARY KEY CLUSTERED (CreationDate, Id) ON ps_VotesDeletes(CreationDate),
    PERIOD FOR SYSTEM_TIME([SysStartTime], [SysEndTime])
) ON ps_VotesDeletes(CreationDate);
GO

/*Load in data!*/
INSERT dbo.Votes_Deletes WITH (TABLOCK)
        (Id, PostId, UserId, BountyAmount, VoteTypeId, CreationDate)
SELECT v.Id, v.PostId, v.UserId, v.BountyAmount, v.VoteTypeId, v.CreationDate
FROM StackOverflow2013.dbo.Votes AS v;


/*Create the history table with no frills*/
/*This probably isn't what you'd wanna do IRL*/
CREATE TABLE dbo.Votes_Deletes_History
(
    Id INT NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    SysStartTime DATETIME2(7) NOT NULL,
    SysEndTime DATETIME2(7) NOT NULL
) ON [PRIMARY];
GO

/*Turn on versioning with the consistency check off*/
/*This probably isn't what you'd wanna do IRL*/
/*The data consistency check might be important*/
ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = ON
 ( HISTORY_TABLE=dbo.Votes_Deletes_History,
   DATA_CONSISTENCY_CHECK= OFF) );



/*If you want to swap partitions in and out, you gotta match EVERYTHING*/
/*Except all the actual partitioning.*/
/*Staging tables aren't often partitioned.*/
/*In this case, the system versioning has to be there too*/
CREATE TABLE dbo.Votes_Deletes_Stage
(
    Id INT NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    SysStartTime DATETIME2(7) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT df_VotesDeletes_Stage_SysStartTime
            DEFAULT SYSDATETIME(),
    SysEndTime DATETIME2(7) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT df_VotesDeletes_Stage_SysEndTime
            DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')
    CONSTRAINT dr_rockzo_Stage PRIMARY KEY CLUSTERED (CreationDate, Id) ON [PRIMARY],
	PERIOD FOR SYSTEM_TIME([SysStartTime], [SysEndTime])
) ON [PRIMARY];

/*What's our partition number for the last year of data?*/
SELECT TOP (1) YEAR(v.CreationDate) AS CreationYear, cs.PartitionID
FROM dbo.Votes_Deletes AS v
CROSS APPLY(VALUES($PARTITION.pf_VotesDeletes(v.CreationDate))) AS cs (PartitionID)
WHERE v.CreationDate >= '20130101';

/*Try this. It won't work with versioning on.*/
ALTER TABLE dbo.Votes_Deletes 
SWITCH PARTITION 8 TO dbo.Votes_Deletes_Stage;

/*Run this, then come back.*/
ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = OFF);

/*Switch data back in?*/
/*Not without a constraint :(*/
ALTER TABLE dbo.Votes_Deletes_Stage 
SWITCH TO dbo.Votes_Deletes PARTITION 8;

/*Add this then try again*/
ALTER TABLE dbo.Votes_Deletes_Stage
ADD CONSTRAINT ck_yrself 
    CHECK (CreationDate >= '20130101' AND CreationDate < '20140101' 
	       AND CreationDate IS NOT NULL);

/*Check in*/
SELECT COUNT(*)
FROM dbo.Votes_Deletes AS vd
WHERE vd.CreationDate >= '20130101';


/*Re-enable temporal-ness*/
ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = ON
 ( HISTORY_TABLE=dbo.Votes_Deletes_History,
   DATA_CONSISTENCY_CHECK= OFF) );

/*Easy reset*/
ALTER TABLE dbo.Votes_Deletes_Stage
DROP CONSTRAINT ck_yrself;