Microsoft SQL Server
임시 테이블

비고

임시 테이블 만들기

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

INSERTS :는 INSERT에서, 시스템은 시스템 클럭에 기초하여 (UTC 시간 영역에서의) 현재의 트랜잭션의 시작 시간에 ValidFrom 열에 대한 값을 설정하고, 9999- 최대 값 ValidTo 열의 값을 할당 12-31. 행이 열린 것으로 표시됩니다.

UPDATES : UPDATE 에서 시스템은 기록 테이블에 행의 이전 값을 저장하고 ValidTo 열 값을 시스템 시계에 따라 현재 트랜잭션의 시작 시간 (UTC 시간대 기준)으로 설정합니다. 이것은 행을 닫은 것으로 표시하고 행은 유효 기간을 기록합니다. 현재 테이블에서 행은 새 값으로 업데이트되고 시스템은 시스템 시계를 기준으로 ValidFrom 열 값을 트랜잭션의 시작 시간 (UTC 시간대)으로 설정합니다. 현재 열의 ValidTo 열에 대한 업데이트 된 행의 값은 최대 값인 9999-12-31로 유지됩니다.

삭제합니다는 DELETE에서, 시스템은 이력 테이블의 행의 이전 값을 저장하고, 시스템 클럭에 기초하여 (UTC 시간 영역에서의) 현재의 트랜잭션의 시작 시간에 ValidTo 열에 대한 값을 설정한다. 이것은 행을 닫은 것으로 표시하고 이전 행이 유효했던 기간이 기록됩니다. 현재 테이블에서 행이 제거됩니다. 현재 테이블의 쿼리는이 행을 반환하지 않습니다. 히스토리 데이터를 처리하는 u 리만이 행이 닫힌 데이터를 리턴합니다.

MERGE : MERGE 에서 MERGE 문에 지정된 작업에 따라 최대 세 개의 명령문 ( INSERT , UPDATE 및 / 또는 DELETE )이 실행될 때와 똑같이 동작합니다.

팁 : 시스템 datetime2 열에 기록 된 시간은 트랜잭션 자체의 시작 시간을 기반으로합니다. 예를 들어, 단일 트랜잭션 내에 삽입 된 모든 행은 SYSTEM_TIME 기간의 시작에 해당하는 열에 동일한 UTC 시간이 기록됩니다.

시간 데이터를 어떻게 조회합니까?

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

특정 시점에 지정된 실제 값을 반환합니다 (FOR SYSTEM_TIME AS OF )

지정된 특정 시점의 실제 (현재) 값을 포함하는 행이있는 테이블을 반환합니다.

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

둘 사이에 SYSTEM_TIME 동안 과

반환되는 행 테이블에는 <end_date_time> 끝점에서 정의 된 위쪽 경계에서 활성화 된 행이 포함된다는 점을 제외하고는 FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time> 설명과 같습니다.

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

FOR SYSTEM_TIME FROM 에

FROM 인수의 <start_date_time> 매개 변수 값 이전에 활성화되기 시작했는지 아니면 <end_date_time> 매개 변수 값 이후에 활성 상태가 종료되었는지에 관계없이 지정된 시간 범위 내에 활성화 된 모든 행 버전의 값이있는 테이블을 반환합니다. TO 인수. 내부적으로는 임시 테이블과 해당 실행 기록 테이블간에 공용체가 수행되고 지정된 시간 범위 내에서 언제든지 활성화 된 모든 행 버전의 값을 반환하도록 결과가 필터링됩니다. FROM 엔드 포인트에 의해 정의 된 하위 경계에서 정확히 활성화 된 행이 포함되며 TO 엔드 포인트에 의해 정의 된 상위 경계에서 정확히 활성화 된 레코드는 포함되지 않습니다.

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

FOR SYSTEM_TIME IN에 포함됨 ( , )

CONTAINED IN 인수의 두 datetime 값으로 정의 된 지정된 시간 범위 내에서 열고 닫힌 모든 행 버전의 값이있는 테이블을 반환합니다. 낮은 경계에서 정확히 활성 상태가되거나 위쪽 경계에서 정확하게 활성 상태가 중단 된 행이 포함됩니다.

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

FOR SYSTEM_TIME ALL

현재 테이블과 히스토리 테이블에 속한 행의 합집합을 리턴합니다.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

메모리 최적화 된 시스템 버전의 임시 테이블 만들기 및 SQL Server 기록 테이블 정리

기본 히스토리 테이블을 사용하여 임시 테이블을 작성하는 것은 이름 지정을 제어하고 시스템에서 여전히 기본 구성으로 히스토리 테이블을 작성하려는 경우 편리한 옵션입니다. 아래 예제에서 새로운 디스크 기반 기록 테이블에 링크 된 새로운 시스템 버전의 메모리 최적화 임시 테이블입니다.

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

SQL Server 기록 테이블 정리 시간 경과에 따라 기록 테이블이 크게 커질 수 있습니다. 히스토리 테이블의 데이터 삽입, 업데이트 또는 삭제가 허용되지 않으므로 히스토리 테이블을 정리하는 유일한 방법은 먼저 시스템 버전 관리를 비활성화하는 것입니다.

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); 가기

히스토리 테이블에서 불필요한 데이터 삭제 :

    DELETE FROM dbo.EmployeeHistory

어디 EndTime <= '2017-01-26 14:00:29';

시스템 버전 관리를 다시 활성화하십시오.

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo]. [EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));

Azure SQL 데이터베이스는 이력 테이블 정리 기능이 내장되어 있기 때문에 Azure SQL 데이터베이스에서 이력 테이블을 정리하는 것은 약간 다릅니다. 첫째, 데이터베이스 레벨에서 일시적 히스토리 보존 정리가 가능해야합니다.

ALTER DATABASE CURRENT

GO TEMPORAL_HISTORY_RETENTION ON GO

그런 다음 표당 보관 기간을 설정합니다.

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 일));

그러면 90 일보다 오래된 기록 테이블의 모든 데이터가 삭제됩니다. SQL Server 2016 온 - 프레미스 데이터베이스는 TEMPORAL_HISTORY_RETENTION 및 HISTORY_RETENTION_PERIOD를 지원하지 않으며 위의 두 쿼리 중 하나가 SQL Server 2016 온 - 프레미스 데이터베이스에서 실행되어 다음과 같은 오류가 발생합니다.

TEMPORAL_HISTORY_RETENTION의 오류는 다음과 같습니다.

Msg 102, Level 15, State 6, Line 34

'TEMPORAL_HISTORY_RETENTION'근처의 구문이 잘못되었습니다.

HISTORY_RETENTION_PERIOD 오류 :

Msg 102, Level 15, State 1, Line 39

'HISTORY_RETENTION_PERIOD'근처의 구문이 잘못되었습니다.