SQL Server System-Versioned Temporal Tables & cdc

於 table 增加兩個欄位來啟用內建的table row log 機制 (僅限 2016 以上版本)

ALTER TABLE dbo.AWBuildVersion ADD

  [TimeStart] DATETIME2(0)  GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeStart DEFAULT ('19000101'),

  [TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeEnd DEFAULT ('99991231 23:59:59'),

  PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]);

 ALTER TABLE dbo.AWBuildVersion

 DROP CONSTRAINT DFT_AWBuildVersion_TimeStart, DFT_AWBuildVersion_TimeEnd;

 

ALTER TABLE dbo.AWBuildVersion  SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.AWBuildVersion_History ) );

第一次修改資料後就會自動產生history table，裡面會有修改前的資料，若有新增欄位等行為也會自動同步

※後續若要調整table scheme 只能透過指令，已經不能再用設計功能處理

※若要知道每個版本是誰修改，要另外增加修改者的欄位，每次更新時記錄更新人員

若是 2016 以前版本，可以使用 cdc (異動資料擷取)

啟用資料庫cdc

sys.sp_cdc_enable_db

啟用資料表cdc (一個欄位上限64KB，超過會造成原本table insert 失敗!!)

EXECUTE sys.sp_cdc_enable_table

    @source_schema = N'dbo',

    @source_name = N'table1',

    @role_name = NULL;

之後異動會於系統資料表出現 dbo_table1_CT，每次異動產生兩筆row

__$operation=1 表示刪除

__$operation=2 表示新增

__$operation=3 表示異動前

__$operation=4 表示異動後

若有更改資料表結構，例如增加欄位，必須重新產生log table，否則不會記錄到新欄位的變化，使用以下指令 disable cdc (會自動 drop log table)

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'student';

EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name   = N'student', @capture_instance = xxx;

取得異動時間

select cdc.lsn_time_mapping.tran_begin_time,...

from xxx

INNER JOIN cdc.lsn_time_mapping ON xxx.__$start_lsn = cdc.lsn_time_mapping.start_lsn

異動資料預設保存三天，若要調整天數使用以下指令 (每個資料庫各別設定，單位是分鐘，43200=30天)

EXEC sys.sp_cdc_change_job @job_type=N'Cleanup' ,@retention=43200

參考來源