SQL. Tabele temporalne, czyli wersjonowanie zmian w tabelach
Kiedy modyfikujemy dane w tabelach tracimy w zasadzie wszystkie, jakiekolwiek informacje o wcześniejszych wersjach danych. Czasami jednak potrzebujemy mieć możliwość dostępu do poprzednich, historycznych wartości danych. Nie trzeba chyba wspominać, kiedy taka potrzeba zachodzi, wymienię tylko :
- inspekcję danych,
- analizę danych w momentach czasowych
- porównanie stanu aktualnego z poprzednim itd.
Od wersji SQL Server 2016 możemy użyć rozwiązania która szerzej jest określana jako wersjonowane systemowo tabele temporalne. Taka tabela zawiera dwie kolumny, które opisują okres ważności oraz połączoną tabelę historii wyposażoną w schemat lustrzany, gdzie przechowywane są poprzednie stany zmienianych wierszy. Kiedy chcemy zmodyfikować dane odwołujemy się do aktualnej (bieżącej) tabeli używając zwykłych poleceń modyfikowania danych (np. UPDATE). SQL Server wtedy, automatycznie aktualizuje kolumny okresu ważności i przenosi starsze wersje rekordów to tabeli historii zmian. Jeżeli odpytujemy bieżącą tabelę z danymi robimy to normalnie tradycyjnie. Ale jak chcemy uzyskać dostęp do starszych danych odpytujemy aktualną (bieżącą) tabelę, z tym że dołączamy dodatkową klauzulę.
W SQL mamy trzy typy tabel temporalnych:
- wersjonowane systemowo – działanie opiera się na czasie systemowym (SQL Serwer obsługuje tylko takie)
- tabele oparte na czasie aplikacji – definiowane a aplikacji ważności wiersza
- tabele bitemporalne – łączą dwa powyższe typy.
Tabele tego typu muszą spełniać kilka warunków:
- Muszą mieć klucz główny
- Muszą mieć dwie kolumny typu DATETIME2
kolumna startowa z opcją GENERATED ALWAYS AS ROW START
kolumna końcowa z opcją GENERATED ALWAYS AS ROW END - oznaczenie kolumn okresu ważności: PERIOD FOR SYSTEM_TIME(kolumnaStartowa,kolumnaKońcowa)
- Opcja SYSTEM_VERSIONING ustawiona na ON
- Tabela historii musi być połączona.
Niektórzy ukrywają kolumny okresu ważności, dla poprawienia czytelności zapytań (one i tak same się uzupełniają).
Oto przykładowe polecenie, które zakłada tabelę Employees oraz przyłączoną do niej tabelę EmployeeHistory.
CREATE TABLE dbo.Employees(
empid INT NOT NULL CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED,
empname VARCHAR(25) NOT NULL,
department VARCHAR(50) NOT NULL,
salary NUMERIC(10, 2) NOT NULL,
validfrom DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
validto DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (validfrom, validto), )
WITH ( SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.EmployeesHistory,
HISTORY_RETENTION_PERIOD = 5 YEARS ) );
empid INT NOT NULL CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED,
empname VARCHAR(25) NOT NULL,
department VARCHAR(50) NOT NULL,
salary NUMERIC(10, 2) NOT NULL,
validfrom DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
validto DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (validfrom, validto), )
WITH ( SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.EmployeesHistory,
HISTORY_RETENTION_PERIOD = 5 YEARS ) );
Tabela temporalna i powiązana z nią tabela historii:
W kolejnym blogu zobaczymy jak przekształcić zwykłą tabelę z danymi w tabelę temporalną.
Więcej informacji na ten i wiele innych tematów na szkoleniu:
Szkolenie: SQL Serwer dla zaawansowanych
