SQL Server 2016 - Temporal Table

Database/MSSQL Server

SQL Server 2016 - Temporal Table

데이터엔지니어 주형권 2016. 4. 12. 11:12

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://msdn.microsoft.com/en-us/library/dn935015.aspx

http://www.infoq.com/news/2015/06/SQL-Server-Temporal

https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/

저작자표시 비영리 변경금지