데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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을 참고한다.

 

아래 스크립트는 시스템 버전 테이블을 생성한다.

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])

);

 

 

[참고자료]

 

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
2050 In-Memory OLTP 환경에서 체크포인트 작업과 디스크 부족 경고 jevida(강성욱) 2017.01.11 1273
2049 Spool 연산자와 추적 플래그 8690 jevida(강성욱) 2017.01.11 1816
2048 .NET 4.6.1에 변경된 Multisubnet 기본 수신기 동작 jevida(강성욱) 2017.01.11 1594
2047 함수 통계 정보 확인 (sys.dm_exec_function_stats) jevida(강성욱) 2017.01.11 1561
2046 NULL 데이터가 포함된 데이터 사용 시 주의점 jevida(강성욱) 2017.01.11 2809
2045 통계정보와 실제 데이터 분포 확인하기 jevida(강성욱) 2017.01.11 1664
2044 SQL Server Failover Cluster 설치시 네트워크 이름으로 인한 설치 오류 jevida(강성욱) 2017.01.11 1456
2043 SSIS 실행 로그 남기기 jevida(강성욱) 2017.01.11 2643
2042 확장이벤트를 사용한 실행 계획 캡처 jevida(강성욱) 2017.01.11 1208
2041 테이블 외래키 트리 확인 및 데이터 삭제하기 jevida(강성욱) 2017.01.11 1738
2040 기본 추적을 사용한 SQL Server 스키마 변경사항 캡처 jevida(강성욱) 2017.01.11 1551
2039 SQL Server 특정 테이블의 모든 컬럼에서 문자열 찾기 jevida(강성욱) 2017.01.11 1648
2038 SQL Server 임시 테이블 특성 jevida(강성욱) 2017.01.11 7090
2037 트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거) jevida(강성욱) 2017.01.11 2129
2036 컬럼스토어 인덱스 성능 (Columnsotre Index Performance) jevida(강성욱) 2017.01.11 3151
2035 In-Memory 최적화 파일 경로 변경 jevida(강성욱) 2017.01.11 1492
2034 SA 계정 이름 변경 및 비활성화 jevida(강성욱) 2017.01.11 2264
2033 SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구 jevida(강성욱) 2017.01.11 1521
2032 XML로 생성된 정보를 테이블로 저장하기(sp_xml_preparedocument) jevida(강성욱) 2017.01.11 2167
» SQL Server 2016 Temporal Table – 데이터 변경 내용 추적 jevida(강성욱) 2017.01.11 1908





XE Login