SE の雑記

BEGIN TRAN
EXEC ('ALTER TABLE [dbo].[TemporalTable] SET (SYSTEM_VERSIONING = OFF)')
EXEC ('DELETE FROM  [dbo].[TemporalTableHistory]  WHERE SysEndTime <= DATEADD(dd, 2, GETDATE())')
EXEC ('ALTER TABLE &#91;dbo&#93;.&#91;TemporalTable&#93; SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableHistory,  DATA_CONSISTENCY_CHECK = OFF ))')
COMMIT TRAN
&#91;/sourcecode&#93;
</pre>
</div>
<p>&#160;</p>
<p>このようなクエリを実行することで、削除中のデータの変更を防止したうえで、データを削除することができます。
  <br />ただし、履歴に大量のデータが格納されているような状態ですとこのような削除を行うと、処理に時間がかかってしまい、オンラインに影響が出る可能性があります。</p>
<p>そのため、瞬時にデータの削除を実施したい場合には、Enterprise Edition で使用することができるパーティショニングと組み合わせる必要があります。</p>
<p>以下のようなクエリで、履歴側のテーブルをパーティショニングし、パーティショニングしたテーブルをスイッチするためのアーカイブテーブルを作成します。</p>
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:42fc5b66-2c25-4f4b-8028-09c4f5e3ff5e" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>
[sourcecode language='sql' ]
IF DB_ID('DemoDB') IS NULL
	CREATE DATABASE DemoDB
GO
USE DemoDB
GO
-- テスト用テーブルの作成
IF OBJECT_ID('TemporalTable_Partition') IS NOT NULL
BEGIN
    ALTER TABLE TemporalTable_Partition SET (SYSTEM_VERSIONING = OFF)
	DROP TABLE TemporalTable_Partition
END
GO
-- テスト用テーブルの作成
IF OBJECT_ID('TemporalTableHistory_Partition') IS NOT NULL
	DROP TABLE TemporalTableHistory_Partition
GO
IF OBJECT_ID('TemporalTableHistory_Archive') IS NOT NULL
	DROP TABLE TemporalTableHistory_Archive
GO
DROP PARTITION SCHEME PS_TemporalTableHistory_Partition
DROP PARTITION FUNCTION PF_TemporalTableHistory_Partition
GO
-- 履歴用テーブルの作成(SysEndTime でパーティショニング)
CREATE TABLE TemporalTableHistory_Partition(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL,
SysEndTime datetime2 NOT NULL
)
GO
CREATE PARTITION FUNCTION [PF_TemporalTableHistory_Partition](datetime2)
AS RANGE RIGHT FOR VALUES
(N'2016-01-01',N'2017-01-01',N'2018-01-01')
GO
CREATE PARTITION SCHEME [PS_TemporalTableHistory_Partition]
AS PARTITION [PF_TemporalTableHistory_Partition]  ALL TO ([PRIMARY])
GO
-- ベーステーブルの作成
CREATE TABLE TemporalTable_Partition(
Col1 int IDENTITY PRIMARY KEY CLUSTERED,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
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.TemporalTableHistory_Partition))
GO
-- 履歴テーブルにインデックスを設定 (履歴テーブルに設定後にインデックスを作成しないと消えてしまっていたので注意)
CREATE CLUSTERED INDEX [CIX_TemporalTableHistory] ON [dbo].[TemporalTableHistory_Partition]
(
[SysEndTime] ASC,
[SysStartTime] ASC
)
WITH (DATA_COMPRESSION = PAGE)
ON [PS_TemporalTableHistory_Partition] ([SysEndTime])
GO
-- 履歴テーブルアーカイブ用
CREATE TABLE TemporalTableHistory_Archive(
Col1 int NOT NULL,
Col2 uniqueidentifier,
Col3 uniqueidentifier,
Col4 nvarchar(10),
SysStartTime datetime2 NOT NULL,
SysEndTime datetime2 NOT NULL
)
GO
CREATE CLUSTERED INDEX [CIX_TemporalTableArchive] ON [dbo].[TemporalTableHistory_Archive]
(
[SysEndTime] ASC,
[SysStartTime] ASC
)
WITH (DATA_COMPRESSION = PAGE)
GO
SELECT
    OBJECT_NAME(sp.object_id)
    , si.name
    , sp.partition_number
    , sp.rows
    , sp.data_compression_desc
    , si.type_desc
FROM
    sys.partitions sp
    LEFT JOIN
        sys.indexes as si
        ON
        si.object_id = sp.object_id
        AND
        si.index_id = sp.index_id
WHERE
    sp.object_id IN (OBJECT_ID(N'dbo.TemporalTableHistory_Partition'))
GO
SELECT *
FROM
	sys.partition_range_values prv
	INNER JOIN
	sys.partition_functions pf
	ON
	prv.function_id = pf.function_id

USE DemoDB
GO
-- テストデータの確認
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4)
VALUES(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA'),
(NEWID(), NEWID(), N'AAAAAA')
WAITFOR DELAY '00:00:01'
BEGIN TRAN
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
COMMIT TRAN
WAITFOR DELAY '00:00:01'
BEGIN TRAN
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
INSERT INTO TemporalTable_Partition (Col2, Col3, Col4) VALUES (NEWID(), NEWID(), N'AAAAAA')
COMMIT TRAN
WAITFOR DELAY '00:00:01'
GO
UPDATE TemporalTable_Partition SET Col2 = NEWID()
GO
SELECT *,$PARTITION.PF_TemporalTableHistory_Partition(SysEndTime) FROM [TemporalTableHistory_Partition]
SELECT * FROM [TemporalTableHistory_Archive]
GO
-- パーティションのスイッチ
ALTER TABLE [dbo].[TemporalTableHistory_Partition]
SWITCH PARTITION 2
TO [dbo].[TemporalTableHistory_Archive]
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))
GO
ALTER PARTITION FUNCTION [PF_TemporalTableHistory_Partition]()
MERGE RANGE(N'2016-01-01')
GO
SELECT *,$PARTITION.PF_TemporalTableHistory_Partition(SysEndTime) FROM [TemporalTableHistory_Partition]
SELECT * FROM [TemporalTableHistory_Archive]
GO
TRUNCATE TABLE [TemporalTableHistory_Archive]
GO
SELECT * FROM [TemporalTableHistory_Archive]