Microsoft SQL Server
Tabele czasowe

Uwagi

UTWÓRZ tabele czasowe

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: W INSERT system ustawia wartość kolumny ValidFrom na czas rozpoczęcia bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego i przypisuje wartość kolumny ValidTo do maksymalnej wartości 9999- 12–31. To oznacza wiersz jako otwarty.

AKTUALIZACJE: W dniu AKTUALIZACJI system przechowuje poprzednią wartość wiersza w tabeli historii i ustawia wartość kolumny ValidTo na czas rozpoczęcia bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Oznacza to, że wiersz jest zamknięty, z zarejestrowanym okresem, dla którego wiersz był ważny. W bieżącej tabeli wiersz jest aktualizowany o nową wartość, a system ustawia wartość kolumny ValidFrom na czas rozpoczęcia transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Wartość zaktualizowanego wiersza w bieżącej tabeli dla kolumny ValidTo pozostaje maksymalną wartością 9999-12-31.

USUŃ : W USUŃ system zapisuje poprzednią wartość wiersza w tabeli historii i ustawia wartość kolumny ValidTo na czas rozpoczęcia bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Oznacza to, że wiersz jest zamknięty, z okresem zarejestrowanym, dla którego poprzedni wiersz był ważny. W bieżącej tabeli wiersz jest usuwany. Zapytania dotyczące bieżącej tabeli nie zwrócą tego wiersza. Tylko zapytania dotyczące danych historycznych zwracają dane, dla których wiersz jest zamknięty.

POŁĄCZ : W POŁĄCZENIU operacja zachowuje się dokładnie tak, jakby wykonano maksymalnie trzy instrukcje ( WSTAW , AKTUALIZACJA i / lub USUŃ ), w zależności od tego, co określono w instrukcji MERGE .

Wskazówka: Czasy zapisane w systemowych kolumnach datetime2 oparte są na czasie rozpoczęcia samej transakcji. Na przykład wszystkie wiersze wstawione w ramach jednej transakcji będą miały ten sam czas UTC zapisany w kolumnie odpowiadający początkowi okresu SYSTEM_TIME .

Jak zapytać o dane tymczasowe?

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;  

Zwraca aktualną wartość określoną w czasie (FOR SYSTEM_TIME AS OF )

Zwraca tabelę z wierszami zawierającymi wartości, które były aktualne (bieżące) w określonym momencie w przeszłości.

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

NA SYSTEM_TIME MIĘDZY I

Taki sam jak powyżej w opisie FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time>, z tą różnicą, że zwrócona tabela wierszy zawiera wiersze, które stały się aktywne na górnej granicy zdefiniowanej przez punkt końcowy <end_date_time>.

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

OD SYSTEM_TIME OD DO

Zwraca tabelę z wartościami dla wszystkich wersji wierszy, które były aktywne w określonym przedziale czasowym, niezależnie od tego, czy zaczęły być aktywne przed wartością parametru <start_date_time> dla argumentu FROM, czy przestały być aktywne po wartości parametru <end_date_time> dla parametru Argument do. Wewnętrznie wykonywane jest połączenie między tabelą czasową a tabelą historii, a wyniki są filtrowane w celu zwrócenia wartości dla wszystkich wersji wierszy, które były aktywne w dowolnym momencie w określonym zakresie czasu. Uwzględniono wiersze, które stały się aktywne dokładnie na dolnej granicy zdefiniowanej przez punkt końcowy FROM, a rekordy, które stały się aktywne dokładnie na górnej granicy zdefiniowanej przez punkt końcowy TO, nie są uwzględnione.

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

ZA SYSTEM_TIME ZAWARTE W ( , )

Zwraca tabelę z wartościami dla wszystkich wersji wierszy, które zostały otwarte i zamknięte w określonym przedziale czasu określonym przez dwie wartości daty i godziny dla argumentu ZAWARTE W. Uwzględniono rzędy, które stały się aktywne dokładnie na dolnej granicy lub przestały być aktywne dokładnie na górnej granicy.

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

NA SYSTEM_TIME WSZYSTKO

Zwraca sumę wierszy należących do bieżącej i tabeli historii.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Tworzenie zoptymalizowanej pod kątem pamięci tabeli czasowej w wersji systemowej i czyszczenie tabeli historii programu SQL Server

Tworzenie tabeli czasowej z domyślną tabelą historii jest wygodną opcją, gdy chcesz kontrolować nazewnictwo i nadal polegać na systemie, aby utworzyć tabelę historii z domyślną konfiguracją. W poniższym przykładzie nowa zoptymalizowana pod kątem pamięci tabela czasowa zoptymalizowana pod kątem systemu połączona z nową tabelą historii opartą na dysku.

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

Czyszczenie tabeli historii programu SQL Server Z czasem tabela historii może znacznie wzrosnąć. Ponieważ wstawianie, aktualizowanie lub usuwanie danych z tabeli historii jest niedozwolone, jedynym sposobem na wyczyszczenie tabeli historii jest najpierw wyłączenie wersji systemu:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); UDAĆ SIĘ

Usuń niepotrzebne dane z tabeli historii:

    DELETE FROM dbo.EmployeeHistory

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

a następnie ponownie włącz wersjonowanie systemu:

ALTER TABLE dbo.Employee

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

Czyszczenie tabeli historii w bazach danych Azure SQL jest nieco inne, ponieważ bazy danych Azure SQL mają wbudowaną obsługę czyszczenia tabeli historii. Po pierwsze, należy włączyć czasowe czyszczenie przechowywania historii na poziomie bazy danych:

ALTER DATABASE CURRENT

USTAW TEMPORAL_HISTORY_RETENTION NA GO

Następnie ustaw okres przechowywania według tabeli:

ALTER TABLE dbo.Employee

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

Spowoduje to usunięcie wszystkich danych z tabeli historii starszych niż 90 dni. Lokalne bazy danych SQL Server 2016 nie obsługują TEMPORAL_HISTORY_RETENTION i HISTORY_RETENTION_PERIOD i jedno z powyższych dwóch zapytań jest wykonywane w lokalnych bazach danych SQL Server 2016, wystąpią następujące błędy.

Dla TEMPORAL_HISTORY_RETENTION błąd będzie:

Msg 102, Level 15, State 6, Line 34

Niepoprawna składnia w pobliżu „TEMPORAL_HISTORY_RETENTION”.

Dla HISTORY_RETENTION_PERIOD błąd będzie:

Msg 102, Level 15, State 1, Line 39

Niepoprawna składnia w pobliżu „HISTORY_RETENTION_PERIOD”.