상세 컨텐츠

본문 제목

MSSQL 데이터 변경 이력 관리 방법 #SQL Server 2016 Temporal Table

카테고리 없음

by A+티스토리 2022. 2. 10. 16:11

본문

반응형

[출처] https://sungwookkang.com/1091

 

SQL Server 2016 Temporal Table – 데이터 변경 내용 추적

SQL Server 2016 Temporal Table – 데이터 변경 내용 추적 Version : SQL Server 2016 SQL Server 2016에서 새롭게 소개된 Temporal Table 대해서 알아본다. 여기서 소개하는 임시테이블은 임시 테이블(Temporal..

sungwookkang.com

 

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

 

 

[참고자료]

 

 

2015-08-05 / 강성욱 / http://sqlmvp.kr



출처: https://sungwookkang.com/1091 [Data Science Lab]

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

[출처] https://burning-dba.tistory.com/75

 

SQL Server 2016 - Temporal Table

SQL Server 2016에서 Temporal Table이라는 기능이 있어서, 소개하고자 합니다. Temporal은 다음과 같은 뜻을 가집니다. - 1. 현세적인, 속세의 2. 시간의; 시간의 제약을 받는 3. 관자놀이께의 Temporal 테이블

burning-dba.tistory.com

 

SQL Server 2016에서 Temporal Table이라는 기능이 있어서, 소개하고자 합니다.

Temporal은 다음과 같은 뜻을 가집니다.

- 1. 현세적인, 속세의   2. 시간의; 시간의 제약을 받는   3. 관자놀이께의 

 

Temporal 테이블은 제가 볼때 엄청난 기능은 아니고, Table의 내용이 변경(UPDATE, DELETE)이 될 경우 그 내용을 기록하는 History (?)성 테이블이라고 생각하면 됩니다.

글로 표현하는 것보다 그림으로 보는 것이 더욱 이해가 될 것 입니다.

제가 생각 할때 이 그림이 가장 맞는거 같습니다. 크게 별다른 기능이 아니고, 단순히 유저가 DELETE , UPDATE 라는 조작을 가하면 테이블에 기록을 남기는 것 입니다.

음... 일종에 트리거 같은 기능을 종속하여, 추가한 것으로 보여집니다. 

 

바로 이 기능을 테스트 해보겠습니다.

 

 

 

Step 1 - 테이블 생성 및 데이터 입력

 

우선 테이블을 생성하고, 데이터를 입력하는데 2개를 생성해 줍니다. 우리가 보기 위한 것은 기록을 검색하고, UPDATE , DELETE 시에 잘 남는지를 보는 것이므로,

생성과 동시에 데이터를 몇개 삽입 하도록 하겠습니다.

 

-- create history table
CREATE TABLE dbo.PriceHistory
	(ID			INT				NOT NULL
	,Product	VARCHAR(50)		NOT NULL
	,Price		NUMERIC(10,2)	NOT NULL
	,StartDate	DATETIME2		NOT NULL
	,EndDate	DATETIME2		NOT NULL
	)
GO

-- insert values for history
INSERT INTO dbo.PriceHistory(ID,Product,Price,StartDate,EndDate)
VALUES	 (1,'myProduct',1.15,'2015-07-01 00:00:00','2015-07-01 11:58:00')
		,(1,'myProduct',1.16,'2015-07-01 11:58:00','2015-07-03 12:00:00')
		,(1,'myProduct',1.18,'2015-07-03 12:00:00','2015-07-05 18:05:00')
		,(1,'myProduct',1.21,'2015-07-05 18:05:00','2015-07-07 08:33:00')


-- create current table to store prices
CREATE TABLE dbo.Price
	(ID			INT				NOT NULL
	,Product	VARCHAR(50)		NOT NULL
	,Price		NUMERIC(10,2)	NOT NULL
	,StartDate	DATETIME2		NOT NULL
	,EndDate	DATETIME2		NOT NULL
	,CONSTRAINT PK_Price PRIMARY KEY CLUSTERED  (ID ASC)
	)
GO

-- insert the current price (make sure start date is not in the future!)
INSERT INTO dbo.Price(ID,Product,Price,StartDate,EndDate)
VALUES	 (1,'myProduct',1.20,'2015-07-07 08:33:00','9999-12-31 23:59:59.9999999')
GO

 

데이터가 잘 들어갔는지 확인해 보겠습니다.

첫번째가 Price 테이블 입니다. 그리고 아래가 PriceHistory 입니다.

 

 

 

 

Step 2 - Temporal Table 만들기 

 

이제 PriceHistory를 Price에 종속 시켜야합니다. 저는 종속이라는 표현을 쓰지만, Temporal 테이블을 생성한다고 하는게 맞겠네요.

아래를 보시면 StartDate 그리고 EndDate를 이용하여, Temporal 테이블의 기준 시간으로 삼습니다. 

-- enable system period columns
ALTER TABLE dbo.Price
ADD PERIOD FOR SYSTEM_TIME (StartDate,EndDate)
GO

-- turn on system versioning
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = ON
	(HISTORY_TABLE=dbo.PriceHistory,DATA_CONSISTENCY_CHECK=ON)
)
GO

 

만약 Temporal 테이블이 정상적으로 만들어졌다면, 아래와 같은 모습을 확인 할 수 있습니다. Price 테이블을 클릭하면, 아래에 PriceHistory 테이블이 

나타나는 것을 확인 할 수 있습니다.

 

위의 2개의 테이블은 따로따로도 조회가 가능하며, Price 테이블의 과거 데이터를 조회 할 수도 있습니다.

 

SELECT * FROM dbo.Price
FOR SYSTEM_TIME AS OF '2015-07-04'
GO


SELECT * FROM dbo.Price
FOR SYSTEM_TIME AS OF '2015-07-03 12:00:00'
GO


SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM '2015-07-06' TO '2015-07-06'
GO

 

검색은 여러가지 가능하며, 부분적으로도 가능하며, BETWEEN 검색도 가능 합니다. 

이렇게 검색을 할 경우 현재 Price 테이블의 데이터를 조회하는 것이 아닌 PriceHistory 테이블을 조회합니다. 

 

위의 실행계획을 보면, PriceHistory를 조회하고 있는 것이 보이실 겁니다. 이전 같은 경우 Log 테이블을 따로 만들고, Join해서 조회할 것을 줄였다? 뭐 이렇게

생각하면 좋을 듯 합니다.

 

 

 

 

Step 3 - 데이터 조작

이제 데이터를 조작해 보겠습니다. 각각 데이터를 조작 할 경우 어떻게 남는지 확인해 보겠습니다.

-- Update
UPDATE dbo.Price 
SET Price = 1.24

-- Delete
DELETE dbo.Price 

-- Insert
INSERT INTO dbo.Price(ID,Product,Price,StartDate,EndDate)
VALUES	 (2,'YouProduct',1.60,'2015-07-07 08:33:00','9999-12-31 23:59:59.9999999');

 

우선 UPDATE 작업을 진행 합니다. 

UPDATE를 진행 할 경우 영향을 2개의 Table이 받는 것을 볼 수 있습니다.

 

보시는 바와 같이 PriceHistory 테이블에 Insert 작업이 수행됩니다. 분명히 저는 Price 테이블에만 Update 하였는데, 자동으로 삽입됩니다.

 

결과를 확인하면 다음과 같습니다.

결과를 보면 위의 Price 테이블의 StartDate가 제가 수행 한 날짜로 Update가 되었습니다.

그리고, 아래의 PriceHistory 테이블에 원래 4개의 행이 있었는데 5개로 증가하였습니다. 기존의 Price 테이블의 데이터 입니다.

 

이번에는 Delete를 수행하겠습니다. 

역시 Delete도 2개의 행이 영향을 받습니다.  실행계획에서도 이를 확인 할 수 있습니다.

또한 PriceHistory 테이블에 1개의 행이 추가된 것을 볼 수 있습니다.

 

마지막으로 Insert를 수행 하겠습니다. Insert는 Price 테이블에 해보겠습니다.

응? 오류가 납니다.... 

 

위에서 우리는 Insert구문에 StartDate와 EndDate의 값도 삽입하도록 Insert 구문에 명시하였습니다. 하지만 이는 우리가 건들일 수 없는 부분 입니다.

이것은 자동으로 SYSTEM 시간에 의해서 찍히므로, 이것을 제외한 Insert 구문을 날려야 합니다.

INSERT INTO dbo.Price(ID,Product,Price)
VALUES	 (2,'YouProduct',1.60);

 

아래와 같이 정상적으로 추가 된 것을 확인 할 수 있습니다.

 

감사합니다.

 

 

 

추가

 

Trigger가 걸리는지 테스트를 요청하셔서, 한번 테스트 해봤습니다.

단순히 테이블을 추가하여, DELETE 시에 Trigger가 정상으로 동작하는지 보겠습니다.

 

테스트를 위해서 몇개의 행을 집어넣고, 테스트 하였습니다. 

 

CREATE TRIGGER DBO.Trg_TEST ON DBO.Price
AFTER DELETE
AS
	BEGIN 
		SET NOCOUNT ON

		INSERT INTO PriceTrigger 
		SELECT ID,'Trigger Test' 
		FROM deleted
	END

GO

 

만들고, DELETE를 이용하여, 행을 한개 지웠습니다.

결과는 아래에 보시는 바와 같습니다. 트리거에도 잘 남았으며, PriceHistory에도 잘 남은 것을 볼 수 있습니다.



출처: https://burning-dba.tistory.com/75 [데이터 엔지니어 군고구마]

반응형
저작자표시

댓글 영역

티스토리툴바