데이터베이스 개발자 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
» SQL Server 2016 Temporal Table – 데이터 변경 내용 추적 jevida(강성욱) 2017.01.11 1916
2030 Error 17053 타사 네트워크 장치의 SMB 파일 공유 오류 jevida(강성욱) 2017.01.11 1152
2029 최신 SQL Server Native Client 설치하기 jevida(강성욱) 2017.01.11 1785
2028 Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인 jevida(강성욱) 2017.01.11 1524
2027 DBCC DROPCLEANBUFFERS가 작동하지 않을 때 jevida(강성욱) 2017.01.11 1680
2026 손상된 부트페이지 복구하기 jevida(강성욱) 2017.01.11 1827
2025 Temp table 객체 생성시 세션간 충돌하지 않는 이유 jevida(강성욱) 2017.01.11 1625
2024 SQL Server 데이터베이스 메일 계정 수정 jevida(강성욱) 2017.01.11 2247
2023 XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기 jevida(강성욱) 2017.01.11 2219
2022 특정 사용자에 대한 트랜잭션 로그 찾기 jevida(강성욱) 2017.01.11 2237
2021 SQL Server I/O 서브시스템 레이턴시 확인 jevida(강성욱) 2017.01.11 1707
2020 실행계획의 물리 및 논리연산자 설명 jevida(강성욱) 2017.01.11 1802
2019 SQL Server Page Life Expectancy (PLE) jevida(강성욱) 2017.01.11 2336
2018 백업 압축과 추적플래그 3042 jevida(강성욱) 2017.01.11 2071
2017 SQL Server에서 MySQL 링크드서버 연결하기 jevida(강성욱) 2017.01.11 4501
2016 SOS_SCHEDURLER_YIELD 대기와 쿼리 식별 jevida(강성욱) 2017.01.11 3413
2015 랜덤 캐릭터 생성하기 jevida(강성욱) 2017.01.11 2217
2014 트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 jevida(강성욱) 2017.01.11 4301
2013 트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기 jevida(강성욱) 2017.01.11 2733
2012 RESOURCE_GOVERNOR_IDLE과 쿼리 성능 jevida(강성욱) 2017.01.11 2024





XE Login