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 система устанавливает значение для столбца ValidFrom в момент начала текущей транзакции (в часовом поясе UTC) на основе системного тактового сигнала и присваивает значение для столбца ValidTo максимальному значению 9999- 12-31. Это знаменует строку открытой.

ОБНОВЛЕНИЯ: В UPDATE система сохраняет предыдущее значение строки в таблице истории и устанавливает значение для столбца ValidTo в момент начала текущей транзакции (в часовом поясе UTC) на основе системных часов. Это означает, что строка закрыта, с периодом, для которого строка была действительной. В текущей таблице строка обновляется с ее новым значением, и система устанавливает значение для столбца ValidFrom для начала транзакции (в часовом поясе UTC) на основе системных часов. Значение обновленной строки в текущей таблице для столбца ValidTo остается максимальным значением 9999-12-31.

DELETES : В DELETE система сохраняет предыдущее значение строки в таблице предыстории и устанавливает значение для столбца ValidTo в момент начала текущей транзакции (в часовом поясе UTC) на основе системных часов. Это означает, что строка закрыта, с периодом, для которого была выполнена предыдущая строка. В текущей таблице строка удаляется. Запросы текущей таблицы не возвратят эту строку. Только запросы, которые обрабатывают данные истории, возвращают данные, для которых строка закрыта.

MERGE : В MERGE операция ведет себя точно так, как если бы выполнялось до трех операторов ( INSERT , UPDATE и / или DELETE ), в зависимости от того, что указано как действия в операторе MERGE .

Совет. Время, записанное в столбцах системы datetime2, основано на времени начала самой транзакции. Например, все строки, вставленные в одну транзакцию, будут иметь одинаковое время UTC, записанное в столбце, соответствующее началу периода SYSTEM_TIME .

Как мне запрашивать временные данные?

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'

ДЛЯ СИСТЕМЫ_TIME МЕЖДУ А ТАКЖЕ

То же, что указано выше в описании FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time>, за исключением того, что таблица возвращаемых строк включает строки, которые стали активными на верхней границе, определенной конечной точкой <end_date_time>.

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

FOR SYSTEM_TIME FROM К

Возвращает таблицу со значениями для всех версий строк, которые были активны в пределах указанного временного диапазона, независимо от того, начали ли они быть активными до значения параметра <start_date_time> для аргумента FROM или перестали быть активными после значения параметра <end_date_time> для К аргументу. Внутри соединение выполняется между временной таблицей и ее исторической таблицей, и результаты фильтруются, чтобы возвращать значения для всех версий строк, которые были активны в любое время в течение указанного диапазона времени. Строки, которые активировались точно на нижней границе, определяемой конечной точкой FROM, включены и записи, которые стали активными точно на верхней границе, определяемой конечной точкой TO, не включены.

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

ДЛЯ SYSTEM_TIME СОДЕРЖАЩИХСЯ ( , )

Возвращает таблицу со значениями для всех версий строк, которые были открыты и закрыты в течение заданного интервала времени, определяемого двумя значениями datetime для аргумента CONTAINED IN. Строки, которые активировались точно на нижней границе или перестали быть активными точно на верхней границе, включены.

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

ДЛЯ 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

WHERE 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

SET TEMPORAL_HISTORY_RETENTION ON GO

Затем установите период хранения для каждой таблицы:

ALTER TABLE dbo.Employee

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

Это приведет к удалению всех данных в таблице предыстории старше 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'.