SQL Server 2016 – Temporal tables

Za nami premiera nowej edycji naszego ulubionego systemu bazodanowego czyli SQL Server 2016. Wraz z tą wersją pojawiło się wiele nowości zarówno w warstwie bazy transakcyjnej OLTP, wewnętrznych struktur, business intelligence jak i zaawansowanej analityki. Wiele zmian pojawiło się również w samych narzędziach towarzyszących poszczególnym technologiom takich jak Management Studio czy też Visual Studio. W związku z tym, że mnogość tych zmian może przytłaczać, postanowiliśmy uporządkować tą wiedzę  pod postacią kolejnych artykułów oraz filmów wideo.

Pierwszym tematem jakim się zajmiemy jest całkowicie nowa funkcjonalność o nazwie Temporal Tables znana również pod nazwą System versioned tables. Technologia ta nie jest nowatorskim pomysłem Microsoft – już w 2011 została opublikowana jako standard przez ANSI. Tabele tego typu oferują nam całe gro możliwości, które można wykorzystać w przeróżnych scenariuszach. Jak więc to działa? Przekonamy się w dalszej części niniejszego arytkułu.

Tabela typu temporal (nie mylić z tabelami tymczasowymi ang. Temporary tables) pozwala na automatyczne przechowywanie zmian zachodzących w danej tabeli, zabezpieczenie tych danych przed modyfikacją przy jednoczesnej transparentności dla istniejących aplikacji.  Dzieje się tak poprzez zastosowanie tzw. tabeli przechowywania historii, która jest bezpośrednio powiązana z tabelą, którą oznaczymy jako temporal.  Tabela historii nie może być bezpośrednio modyfikowana dzięki czemu dane historyczne są automatycznie zabezpieczone przed niechcianą modyfikacją. Schemat działania tabel typu temporal został przedstawiony poniżej (na podstawie msdn.microsoft.com):

W momencie gdy dane w ramach tabeli są  usuwane, to operacja ta zostanie wykonana na tabeli bazowej, jednocześnie wiersze usuwane zostaną automatycznie przeniesione do tabeli historii. Możecie zauważyć tutaj pewną analogię do tabeli deleted dostępnej już wcześniej w SQL Server.   Analogicznie przeprowadzana jest aktualizacja –  tabela bazowa zostaje zaktualizowana, a wiersz przed aktualizacją (ze starymi wartościami) zostanie przeniesiony do tabeli historii. Obie tabele – zarówno bieżąca jak i historii opisane są zakresem dat mówiącym o tym od kiedy do kiedy dany rekord był aktualny. Sprawdźmy te możliwości na konkretnych przykładach.

Pierwszym krokiem jest stworzenie temporal table. Możemy to osiągnąć na kilka różnych sposobów – przed zapoznaniem się z każdym z nich najpierw stwórzmy bazę danych w której będziemy wykonywać nasze skrypty:

CREATE DATABASE [TemporalTablesDemo]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TemporalTablesDemo', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\TemporalTablesDemo.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'TemporalTablesDemo_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\TemporalTablesDemo_log.ldf' , SIZE = 16384KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Następnie przejdźmy do stworenia naszej pierwszej tabeli typu temporal:

--tabela typu temporal z anonimową tabelą historii
USE TemporalTablesDemo
GO
CREATE TABLE dbo.Person   
(    
     PersonID int NOT NULL PRIMARY KEY CLUSTERED  
   , FirstName nvarchar(50) NOT NULL  
   , LastName nvarchar(50) NOT NULL
   , City nvarchar(50) NOT NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON)   
;  
GO

Jak możecie zauważyć definicja tabeli temporal jest niemal identyczna jak tradycyjnej tabeli. Ważny jest tutaj parametr SYSTEM_VERSIONING ustawiony na ON. Dzięki niemu SQL Server wie, że chcemy stworzyć tabelę temporalną. Kolejnym wymogiem przy tworzeniu tego obiektu są dwa atrybuty, o których już wspominałem, mające za zadanie przechowywać ramy czasowe w jakich konkretne rekordy są/były aktualne. Ważne jest to aby były to atrybuty nie przyjmujące wartości NULL o typie datetime(0). Aby oznaczyć konkretny atrybut jako kolumnę startową należy do jej definicji dodać GENERATED ALWAYS AS ROW START, a dla kolumny z datą końcową analogicznie GENERATED ALWAYS AS ROW END. Ponadto musimy podać obie kolumny w klauzuli PERIOD FOR SYSTEM_TIME(kolumna z datą startową, kolumna z datą końcową). To by było na
tyle – dzięki tej definicji mamy do dyspozycji w pełni działającą tabelę temporalną – proste prawda?

Po wykonaniu powyższego skryptu w Management Studio możemy zobaczyć naszą tabelę. Wizualnie możemy ocenić, iż jest to tabela wersjonowana dzięki symbolowi małego zegarka oraz napisowi System-Versioned tak jak zostało to przedstawione na poniższym zrzucie ekranowym. Po rozwinięciu naszej tabeli zobaczymy powiązaną tabelę historii.  Nazwa tabeli nie została przez nas nadana jawnie, dlatego też SQL Server uznał ją za anonimową i nadał nazwę automatycznie.

Możemy temu bardzo szybko zaradzić poprzez jawne wskazanie nazwy tabeli. Jednak zanim to zrobimy usuńmy istniejącą tabelę dbo.Person. Zapewne większość z was pomyśli o wykorzystaniu standardowego DROP TABLE – spróbujmy:

DROP TABLE dbo.Person
GO

w rezultacie otrzymaliśmy błąd:

Czy to oznacza, iż nie możemy usuwać tego typu tabel? Oczywiście, że możemy tylko wcześniej musimy wyłączyć wersjonowanie.

ALTER TABLE dbo.Person
SET(SYSTEM_VERSIONING=OFF)

Powyższa komenda nie tylko wyłączy cały mechanizm ale również “rozdzieli” tabelę historii od tabeli bazowej tj. informacje, które do momentu wyłączenia mechanizmu wersjonowania zostały zebrane nie zostaną usunięte.

Po wyłączeniu mechanizmy możemy oczywiśćie usunąć w miarę potrzeb tabelę dbo.Person oraz odpowiadającą jej tabelę historii standardowym DROP TABLE. Kolejnym krokiem po usunięciu tabel jest stworzenie jej na nowo wraz z jawnym wskazaniem nazwy tabeli historii wraz z obligatoryjnym schematem tak jak zostało to przedstawione poniżej:

CREATE TABLE dbo.Person   
(    
     PersonID int NOT NULL PRIMARY KEY CLUSTERED  
   , FirstName nvarchar(50) NOT NULL  
   , LastName nvarchar(50) NOT NULL
   , City nvarchar(50) NOT NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory))   
;  
GO

Tworzenie tabeli omawianego typu jest bardzo proste. Równie prosta jest konwersja istniejącego obiektu na tabelę temporal – wystarczy, że dodamy do niej wymagane komponenty:

ALTER TABLE dbo.Person   
   ADD   
      SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN    
           CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()  
      , SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN    
           CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),   
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   
GO

Dodatkowo dodamy wartości domyślne do obu nowododanych kolumn tak aby istniejące wiersze nie miały w tych kolumnach NULL.Po tych działaniach  możemy włączyć wersjonowanie:

ALTER TABLE dbo.Person  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory))

Jako tabelę historyczną możemy podpiąć już istniejącą tabelę z danymi. Trzeba jedynie zapewnić następujące warunki:

• Liczba i nazwy kolumn są identyczne pomiędzy tabelą bazową i historyczną
• Typy danych pomiędzy poszczególnymi kolumnami tabel są takie same
• Kolumny techniczne z danymi zakresu są ustawione na NOT NULL.
• Tabela bazowa ma klucz główny, a tabela historii  go nie ma
• Tabela historii nie ma kolumny identity ani wyzwalaczy
• Tabela historii nie ma zdefiniowanych ogrniaczeń (constraints) – jedynie wartości domyślne są dozwolone
• Tabela historii nie jest umieszczona w grupie plików tylko do odczytu ani nie ma zdefiniowanych mechanizmów śledzenia (change tracking, cdc)

Jak widać lista ograniczeń jest dosyć długa i restrykcyjna jednak jak najbardziej możliwa do spełnienia. Domyślnie powyższe ograniczenia są sprawdzane, możemy w uzasadnionych przypadkach wyłączyć sprawdzanie poprzez ustawienie DATA_CONSISTENCY_CHECK ustawione na OFF w definicji podpięcia tabeli historii. Dodatkowo tabele temporalne są sprawdzane pod kątem konsystencji w ramach komendy DBCC CHECKCONSTRAINTS.

Powyżej w ramach definicji dodawanych kolumn użyłem słowa kluczowego HIDDEN przy nowych kolumnach – oznacza ono , że kolumny te będą niewidoczne dla zapytań, które jawnie ich nie wywołują – zobaczmy to na przykładzie. Wykonajmy zapytanie, które nie odwołuje się do kolumn technicznych wprost – dobrym przykładem będzie użycie gwiazdki:

SELECT
	* 
FROM 
	dbo.Person

W rezultacie otrzymaliśmy wszystkie kolumny oprócz tych, które oznaczyliśmy jako hidden. Ma to na celu zapewnienie transparentności dla już istniejących aplikacji.

Przetestujmy teraz jak działa opisywany przez nas mechanizm w praktyce. Abyśmy mogli we właściwy sposób zobrazować sposób działania musimy wstawić do naszej tabeli dane. Uruchomimy dwie poniższe komendy, które wstawią dwa wiersze do tabeli dbo.Person.

INSERT INTO dbo.Person
(
	PersonID,
	FirstName,
	LastName,
	City
)
VALUES
(
	1,
	N'Jan',
	N'Kowalski',
	N'Katowice'
),
(
	2,
	N'Piotr',
	N'Nowak',
	N'Warszawa'
)
GO

Sprawdźmy czy dane rzeczywiście trafiły do tabeli:

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.Person

Dane trafiły we właściwe miejsce. Dodatkowo możemy zauważyć, iż kolumny techniczne zostały automatycznie wypełnione swoimi wartościami domyślnymi. Narazie nic w tym nadzwyczajnego – zmodyfikujmy dane w tabeli aby zobaczyć nową technologię w akcji. Aby to osiągnąć wykonamy kolejno UPDATE, DELETE, INSERT:

UPDATE dbo.Person
SET
	City='Gdańsk'
WHERE
	LastName='Kowalski'

DELETE FROM dbo.Person
WHERE PersonID=2

INSERT INTO dbo.Person
(
	PersonID,
	FirstName,
	LastName,
	City
)
VALUES
(
	3,
	N'Katarzyna',
	N'Lewandowska',
	N'Lublin'
)

Odpytajmy naszą tabelę standardową komendą SELECT:

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime

FROM
	dbo.Person

Zestaw rekordów jaki otrzymaliśmy nie różni się niczym innym od standardowej tabeli. Jednak nie zapominajmy o tabeli historycznej  jaka została stworzona podczas włączenia wersjonowania – odpytajmy ją:

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.PersonHistory

Stara wersja zaktualizowanego rekordu oraz usunięty rekord znajdują się tam wraz z opisem w postaci kolumn technicznych. Najwazniejsze jest to, że cały ten mechanizm jest dosyć lekki dla systemy bazodanowego i mimo, iż wykonuje on dodatkową pracę to i tak jest to dużo mniej obciążające działanie niż np. zestaw wyzwalaczy pozwalających osiągnąć podobny efekt.  Ale temporal tables to nie tylko proste odpytywanie tabeli bazowej i historycznej – do dyspozycji mamy również cały zestaw nowych typów zapytań. Wiążą się one z następującymi słowami kluczowymi poprzedzonymi klauzulą FOR SYSTEM_TIME:

• ALL
• AS OF
• FROM TO
• BETWEEN AND
• CONTAINED IN

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.Person
FOR SYSTEM_TIME ALL

ALL pozwala nam na wyświetlanie wszystkich zmian historycznych oraz ich bieżących wersji:

Po podejrzeniu planu zapytania zobaczymy, że SQL Server wykorzystuje prostą unię zapytań na tabeli bazowej i historycznej.

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.Person
FOR SYSTEM_TIME AS OF '2016-06-06 18:02'

W rezultacie wykonania zapytania z klauzulą AS OF otrzymujemy stan tabeli na konkretny moment:

Na planie wykonania zobrazowano mechanizm działania, który jak można się domyśleć zawsze działa na podstawie unii tabeli bazowej i historycznej. Baza źródłowa odpytywana jest w celu pobrania wierszy, które na porządany moment były w tabeli w takiej samej formie w jakiej są teraz. Wiersze, które od tamtej pory uległy zmianie są pobierane z tabeli historycznej.

Kolejnymi ciekawymi klauzulami są FROM TO oraz BETWEEN AND, które działają w sposób analogiczny tj. zwracają historię zmian w podanym okresie. Jedyną różnicą pomiędzy nimi jest to, że FROM TO nie zwraca wierszy, które stały się aktywne dokładnie w czasie podanym jako górna granica – BETWEEN AND zwróci takowe wiersze.

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.Person
FOR SYSTEM_TIME FROM '2016-06-04 11:51' TO '2016-06-08 21:53'

Ostatnią dostępną klauzulą jest CONTAINED IN który zwraca wiersze, które zostały otwarte lub zamknięte w podanym jako paramtery zakresie czasu. Wiersze otwarte lub zamknięte dokładnie w czasie podanym jako dolna granica lub górna granica również znajdą się w zwróconym rezultacie.

SELECT 
	PersonID,
	FirstName,
	LastName,
	City,
	SysStartTime,
	SysEndTime
FROM
	dbo.Person
FOR SYSTEM_TIME CONTAINED IN ('2016-06-06','2016-06-06 18:02:56.1589974')

Temporal tables to bardzo użyteczna technologia. Scenariuszy użycia jest bardzo wiele od zastosowań Business Intelligence w modelowaniu historii zmian (Slowly Changing Dimensions) po audyty w bazach transakcyjnych po odtwarzanie uszkodzonych rekordów z bazy historycznej. Dodatkowo mechanizm ten charakteryzuje się lekkością i transparentnością dla istniejących aplikacji dzięki czemu zapewne znajdzie wielu zwolenników. Musimy sobie jednak zdawać sprawę, iż rejestrowanie zmian w tabeli historii obarczone jest kilkoma wadami. Przede wszystkim technologia ta może mieć znaczenie jeśli chodzi o miejsce na dysku – mimo, że strony danych tabel historycznych są domyślnie kompresowane lub możemy na nich założyć indeks kolumnowy, który oprócz bardzo dobrego stopnia kompresji zapewni nam bardzo dobrą wydajność. Ponadto pamiętajmy, iż każda zmiana w tabeli źródłowej będzie wiązać się z dodatkowym narzutem, który w niektórych przypadkach możę powodować problemy wydajnościowe, szczególnie gdy tabela bazowa zawiera kolumny dużego typu jak np. nvarchar(max) czy varbinary(max), które są w pełni obsługiwane. Mimo, iż temporal tables sa transparentne dla aplikacji to nie mogą być użyte poprzez linked server czy widoki zmaterializowane co w niektórych przypadkach jest poważnym problemem. To tylko niektóre z wad wersjonowanych tabel ale mimo wszystko wydaje mi się, że ilość zalet przwewyższa wady i technologia ta znajdzie szerokie zastosowanie.