Microsoft SQL Server
Tabelle temporali

Osservazioni

CREA tabelle temporali

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

INSERTI: su un INSERT , il sistema imposta il valore per la colonna ValidFrom sull'ora di inizio della transazione corrente (nel fuso orario UTC) in base all'orologio di sistema e assegna il valore per la colonna ValidTo al valore massimo di 9999- 12-31. Questo contrassegna la riga come aperta.

AGGIORNAMENTI: in un UPDATE , il sistema memorizza il valore precedente della riga nella tabella cronologia e imposta il valore per la colonna ValidTo sull'ora di inizio della transazione corrente (nel fuso orario UTC) in base all'orologio di sistema. Questo contrassegna la riga come chiusa, con un periodo registrato per il quale la riga era valida. Nella tabella corrente, la riga viene aggiornata con il suo nuovo valore e il sistema imposta il valore per la colonna ValidFrom per l'ora di inizio della transazione (nel fuso orario UTC) in base all'orologio di sistema. Il valore per la riga aggiornata nella tabella corrente per la colonna ValidTo rimane il valore massimo di 9999-12-31.

DELETE : su un CANC , il sistema memorizza il valore precedente della riga nella tabella cronologia e imposta il valore per la colonna ValidTo sull'ora di inizio della transazione corrente (nel fuso orario UTC) in base all'orologio di sistema. Questo contrassegna la riga come chiusa, con un periodo registrato per il quale la riga precedente era valida. Nella tabella corrente, la riga viene rimossa. Le query della tabella corrente non restituiranno questa riga. Solo le query che trattano i dati della cronologia restituiscono i dati per i quali viene chiusa una riga.

MERGE : su MERGE , l'operazione si comporta esattamente come se fossero eseguite fino a tre istruzioni (un INSERT , un UPDATE e / o un DELETE ), a seconda delle azioni specificate nell'istruzione MERGE .

Suggerimento: i tempi registrati nelle colonne datetime2 del sistema si basano sull'ora di inizio della transazione stessa. Ad esempio, tutte le righe inserite all'interno di una singola transazione avranno la stessa ora UTC registrata nella colonna corrispondente all'inizio del periodo SYSTEM_TIME .

Come posso interrogare i dati temporali?

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;  

Restituisce il valore attuale specificato nel tempo (FOR SYSTEM_TIME AS OF )

Restituisce una tabella con righe contenenti i valori effettivi (correnti) nel momento specifico nel passato.

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

PER SYSTEM_TIME TRA E

Come sopra indicato in FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time> description, ad eccezione della tabella di righe restituite include le righe che sono diventate attive sul limite superiore definito dall'endpoint <end_date_time>.

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

PER SYSTEM_TIME FROM A

Restituisce una tabella con i valori per tutte le versioni di riga attive nell'intervallo di tempo specificato, indipendentemente dal fatto che inizino a essere attive prima che il valore del parametro <start_date_time> per l'argomento FROM sia cessato dopo il valore del parametro <end_date_time> per A argomento. Internamente, viene eseguita un'unione tra la tabella temporale e la sua tabella cronologia ei risultati vengono filtrati per restituire i valori per tutte le versioni di riga attive in qualsiasi momento durante l'intervallo di tempo specificato. Le righe che sono diventate attive esattamente sul limite inferiore definito dall'endpoint FROM sono incluse e i record che sono diventati attivi esattamente sul limite superiore definito dall'endpoint TO non sono inclusi.

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

PER SYSTEM_TIME CONTENUTO IN ( , )

Restituisce una tabella con i valori per tutte le versioni di riga aperte e chiuse nell'intervallo di tempo specificato definito dai due valori datetime per l'argomento CONTAINED IN. Le righe che sono diventate attive esattamente sul limite inferiore o sono state disattivate esattamente al limite superiore sono incluse.

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

PER SYSTEM_TIME ALL

Restituisce l'unione di righe che appartengono alla tabella corrente e alla cronologia.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Creazione di una tabella temporale con versione di sistema ottimizzata per la memoria e pulizia della tabella di cronologia di SQL Server

La creazione di una tabella temporale con una tabella cronologica predefinita è un'opzione utile quando si desidera controllare la denominazione e continuare a fare affidamento sul sistema per creare la tabella cronologica con la configurazione predefinita. Nell'esempio seguente, una nuova tabella temporale ottimizzata per la memoria con versione di sistema collegata a una nuova tabella di cronologia basata su disco.

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

Pulizia della tabella della cronologia di SQL Server Nel corso del tempo la tabella della cronologia può aumentare in modo significativo. Poiché l'inserimento, l'aggiornamento o l'eliminazione dei dati dalla tabella della cronologia non sono consentiti, l'unico modo per ripulire la tabella della cronologia è innanzitutto disabilitare il controllo delle versioni del sistema:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); PARTIRE

Elimina i dati non necessari dalla tabella della cronologia:

    DELETE FROM dbo.EmployeeHistory

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

e quindi riattivare il controllo delle versioni del sistema:

ALTER TABLE dbo.Employee

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

La pulizia della tabella della cronologia nei database SQL di Azure è leggermente diversa, poiché i database SQL di Azure hanno il supporto integrato per la pulizia della tabella della cronologia. Innanzitutto, è necessario abilitare la pulizia della cronologia temporale a livello di database:

ALTER DATABASE CURRENT

Imposta TEMPORAL_HISTORY_RETENTION ON GO

Quindi imposta il periodo di conservazione per tabella:

ALTER TABLE dbo.Employee

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

Questo eliminerà tutti i dati nella tabella della cronologia più vecchi di 90 giorni. I database on-premise di SQL Server 2016 non supportano TEMPORAL_HISTORY_RETENTION e HISTORY_RETENTION_PERIOD e una delle due query precedenti viene eseguita nei database on-premise di SQL Server 2016 si verificheranno i seguenti errori.

Per TEMPORAL_HISTORY_RETENTION l'errore sarà:

Msg 102, Level 15, State 6, Line 34

Sintassi errata vicino a "TEMPORAL_HISTORY_RETENTION".

L'errore HISTORY_RETENTION_PERIOD sarà:

Msg 102, Level 15, State 1, Line 39

Sintassi errata in prossimità di "HISTORY_RETENTION_PERIOD".