SQL Server 2016 Temporal Table – 데이터 변경 내용 추적
- Version : SQL Server 2016
SQL Server 2016에서 새롭게 소개된 Temporal Table 대해서 알아본다. 여기서 소개하는 임시테이블은 임시 테이블(Temporal Table)과 임시 테이블(temporary tables)을 착각하지 않도록 주의해야 한다.
SQL Server 2016의 Temporal Table은 테이블의 기록을 데이터로 보존할 수 있도록 하는 시스템 테이블의 새로운 이름이다. 일반 테이블은 현재 데이터를 반환 할 수 있지만 시스템 테이블은 업데이트와 삭제된 버전의 데이터를 조회할 수 있다. 만약 데이터를 5에서 10으로 변경하는 경우 일반 테이블을 조회하면 10이라는 값을 검색할 수 있지만 임시테이블(히스토리 테이블)은 변경된 기록을 유지하여 이전 값인 5를 검색할 수 있다. 이 히스토리 테이블은 레코드가 활성화 된 때를 표시하는 시작 및 종료 데이터와 함께 이전 데이터를 저장한다.
[시스템 버전 테이블 만들기]
새 임시 테이블을 생성 할 때 전체 조건의 몇 가지 사항을 충족해야 한다.
- 기본 키를 정의 해야 한다.
- 두 열은 Datetime2 타입의 시작 및 종료 날짜를 기록하도록 정의되어야 한다. 이 열은SYSTEM_TIME 기간 열이라고 한다.
- INSTEAD OF 트리거는 허용되지 않는다.
- In-Memory OLTP는 사용할 수 없다.
다음은 몇 가지 제한 사항이다.
- 임시테이블 및 히스토리 테이블은 파일 테이블일 수 없다.
- 히스토리 테이블은 constraints 제약이 없다.
- INSERT 및 UPDATE 문은 SYSTEM_TIME 기간 열을 참조 할 수 없다.
- 히스토리 테이블의 데이터는 수정할 수 없다
제약사항에 대한 자세한 내용은 MSDN을 참고한다.
- Temporal Tables : https://msdn.microsoft.com/en-us/library/dn935015.aspx
아래 스크립트는 시스템 버전 테이블을 생성한다.
CREATE TABLE dbo.TestTemporal ( ID int primary key ,A int ,B int ,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON ); |
테이블이 생성되었을 때 히스토리 테이블은 dbo.MSSQL_TemporalHistoryFor_XXXXXX로 생성되는데 XXXXX는 개체 ID이다. 히스토리 테이블의 이름을 지정하지 않으면 SQL Server는 자동으로 다음과 같은 구조를 생성한다.
히스토리 테이블 컬럼은 동일한 세트를 가지고 있으며 자신만의 인덱스와 통계 세트가 있다. 이러한 히스토리 테이블에 클러스터 컬럼스토어 인덱스를 생성하면 성능을 크게 향상 시킬 수 있다.
이제 테이블에 데이터를 삽입하여 테이블의 시간적 버전 기능을 테스트한다.
-- Initial Load INSERT INTO dbo.TestTemporal(ID, A, B) VALUES (1,2,3) ,(2,4,5) ,(3,0,1);
SELECT * FROM dbo.TestTemporal; |
이제 하나의 행을 삭제하고 다른 데이터는 업데이트 작업을 진행 한다.
-- Modify Data DELETE FROM dbo.TestTemporal WHERE ID = 2;
UPDATE dbo.TestTemporal SET A = 5 WHERE ID = 3;
SELECT * FROM dbo.TestTemporal; |
히스토리 테이블을 조회해보면 이전 버전의 데이터를 확인할 수 있다.
select * from dbo.MSSQL_TemporalHistoryFor_1253579504 |
[시스템 버전 테이블의 스키마 변경]
시스템 버전 테이블을 사용하는 경우 테이블 수정이 제한된다.
- ALTER TABLE….REBUILD
- CREATE INDEX
- CREATE STATISTICS
다른 모든 스키마 변경도 허용되지 않는다. 예를 들면 임시테이블 삭제도 허용되지 않는다.
drop table dbo.TestTemporal |
메시지 13552, 수준 16, 상태 1, 줄 14 테이블 삭제 작업은 시스템 버전 관리 임시 테이블에서 지원되는 작업이 아니므로 'SW_Test.dbo.TestTemporal' 테이블에서 테이블 삭제 작업을 수행할 수 없습니다. |
히스토리 테이블에 새로운 열을 추가작업이나 스키마 변경을 위해서는 시스템 버전을 먼저 제거해야 한다.
ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF); |
스키마 변경 후 히스토리 테이블은 동기화를 유지하기 위해 시스템 버전을 다시 시작하여야 한다.
ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.MSSQL_TemporalHistoryFor_1253579504,DATA_CONSISTENCY_CHECK=[ON/OFF]) ); |
[참고자료]
- Temporal Tables : https://msdn.microsoft.com/en-us/library/dn935015.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
