Temporal Tables – Historia zmian w SQL Server

Wprowadzenie

W dwóch ostatnich wpisach na blogu omawiałem zapis historii zmian danych w aplikacji z wykorzystaniem mechanizmu Audit z Entity Framework Plus. Mechanizm tamten działał w momencie, gdy korzystaliśmy z Entity Framework. Jednak nie zawsze chcemy lub możemy korzystać z tego ORMa. Dlatego w dzisiejszym wpisie chcę Ci pokazać mechanizm, który działa zupełnie inaczej i jest dostępny w samym silniku SQL Server. Mechanizm ten nazywa się Temporal Tables i jest dostępny od wersji 2016.

Temporal Tables

Microsoft w wersji 2016 dodał mechanizm Temporal Tables, który umożliwia nam zapis historii zmian danych w dodatkowej tabeli. Dodatkowa tabela ma taką samą strukturę, jak tabele z aktualnymi danymi. Natomiast SQL Server automatycznie dodaje do niej informacje w momencie zmiany danych w pierwotnej tabeli.

Aby ten mechanizm działał, musi zostać spełnione kilka warunków. Przede wszystkim we właściwej tabeli musimy mieć dodatkowe dwie kolumny, które określają, w jakim czasie (UTC) dane są aktualne. Na ogół możemy spotkać takie nazwy kolumn, jak ValidFrom/ValidTo lub SysStartTime/SysEndTime.

W momencie dodawania nowego rekordu do tabeli SQL Server automatycznie ustawia początek okresu na aktualny czas w strefie UTC. Natomiast koniec ustawiany jest na wartość 9999-12-31 23:59:59.99, która w praktyce określa, że wartość rekordu jest cały czas aktualna.

W momencie jakiejś operacji na rekordzie (np. aktualizacji danych) SQL Server automatycznie przekopiowuje starą wersję rekordu do tabeli z historią. W kopii ustawia koniec okresu ważności danych na aktualny czas i ten sam czas ustawia jako początek ważności rekordu w głównej tabeli.

Dodatkowo SQL Server udostępnia nam składnię, za pomocą której możemy wyciągnąć wartość rekordu z określonego momentu w czasie.  Takie zapytanie jest wykonywane automatycznie na obu tabelach i nie musimy robić tego ręcznie.

Skoro wiemy, jak działa mechanizm Temporal Tables, zobaczmy, jak z niego skorzystać.

Darmowy kurs Visual Studio

Pracując z setkami programistów, zauważyłem, że większość osób nie pracuje efektywnie w Visual Studio. W skrajnych przypadkach korzystali z kopiowania z wykorzystaniem menu Edit. Wiem, że to dziwne, ale naprawdę niektórzy tak pracują. Dlatego postanowiłem stworzyć kurs Visual Studio – aby pomóc koleżankom i kolegom w efektywniejszej pracy.

Przygotowałem 20 lekcji e-mail, w których pokażę Ci, w jaki sposób pracować efektywniej i szybciej w Visual Studio. Poznasz dodatki, bez których nie wyobrażam sobie pracy w tym IDE.

Po więcej informacji zapraszam na dedykowaną stronę kursu: Darmowy Kurs Visual Studio.

Quiz C#

Ostatnio przygotowałem również quiz C#, w którym możesz sprawdzić swoją wiedzę. Podejmiesz wyzwanie?

Dodanie tabeli z historią

Z mechanizmu Temporal Tables możemy skorzystać na dwa sposoby: włączyć go od razu podczas tworzenia tabeli lub dodać go do już istniejącej.

W przykładzie będziemy bazowali na tabeli Products, które będzie zawierała trzy kolumny: Id, Name oraz Description. Plus kolumny wymagane przez Temporal Tables.

Stworzenie nowej tabeli z włączonym mechanizmem historii wygląda tak:

Początek instrukcji CREATE TABLE jest taki sam jak zwykle. Zmiany pojawiają się w momencie dodania dodatkowych kolumn. W przykładzie użyłem nazw ValidFrom oraz ValidTo dla początku oraz końca ważności rekordu. Wartość kolumn jest generowana automatycznie przez serwer bazy danych i oznaczona jako początek i koniec ważności rekordu.

Na samym końcu za pomocą klauzy WITH mówimy, że tabele mają mieć włączone wersjonowanie oraz podajemy nazwę tabeli z historią. Warto tutaj podać swoją nazwę, ponieważ wygenerowana nie jest zbyt fajna (np. MSSQL_TemporalHistoryFor_565577053 – domyśl się później, że jest to tabela z historią dla tabeli Products).

Drugim sposobem jest dodanie historii do istniejącej tabeli z wykorzystaniem ALTER TABLE:

Zapis danych w historii

Mając już przygotowaną tabelę z historią, możemy zobaczyć, w jaki sposób SQL Server zapisuje dane w historii.

Zaczniemy od prostego inserta:

W efekcie w tabeli właściwej oraz w historii otrzymamy coś takiego:

sql history insert

Tabela z danymi zawiera jeden wpis aktualny, natomiast historia jest pusta.

W przypadku update pojawi się już wpis w historii:

sql history update

Warto zwrócić uwagę, że ValidTo rekordu w historii ma taką samą wartość jak ValidFrom w normalnej tabeli.

Ciekawa sytuacja pojawia się w przypadku usuwania rekordu. W tabeli z danymi rekord znika, natomiast w historii dane pozostają. Plus pojawia się informacja o ostatnim ważnym stanie obiektu z datą, do której obowiązywał:

sql history delete

Jak widać, informacje ładnie zapisują się w tabeli z historią.

Pobieranie danych

Poza samym zapisaniem historii SQL Server udostępnia również składnię, za pomocą której możemy pobierać dane.

Dzięki FOR SYSTEM_TIME AS OF możemy pobrać wartość danych z określonego momentu w czasie, co widać poniżej:

sql history of time

Można również wyciągnąć historię dla jakiegoś okresu:

sql history from

Czy wręcz całą historię rekordu:

sql history all

Podsumowanie

Temporal Tables z SQL Server jest bardzo ciekawym mechanizmem. Za jego pomocą możemy zapisywać historię rekordów w sposób automatyczny. Podejście to jest o tyle fajne, że jest zaimplementowane w samym silniku, czyli niezależnie od tego, w jaki sposób pracujemy na danych, historia się zapisze. W przypadku Audit z Entity Framework Plus dane zapisane poza aplikacją (np. bezpośrednio w Management Studio) nie zostaną w historii. Z drugiej strony za każdym razem zapisywane są wszystkie dane. Nie mamy za bardzo możliwości konfigurować tego, że na przykład zapisujemy tylko dane z określonej kolumny, jak jest to możliwe w Audit.

Dodatkowo warto pamiętać o tym, że z czasem liczba zapisanych danych może być duża, przez co wielkość bazy może wzrosnąć. W takim przypadku możemy się postarać i zapisywać tylko np. ostatnie trzy wersje. Ale to już temat na oddzielny wpis.

W kolejnym wpisie pokażę, w jaki sposób wykorzystać Temporal Tables w aplikacji .NET.

3 thoughts on “Temporal Tables – Historia zmian w SQL Server

  • Pingback: dotnetomaniak.pl
  • Czy sama zmiana struktury tabeli nie jest wtedy problematyczna? Czy dane w histori pozostaną po takiej operacji?Czy używałeś tego mechanizmu w komercyjnym projekcie?

    Czekam z niecierpliwością na kolejny wpis, ponieważ jak dla mnie włączenie tego powodowało problemy z aktualizają struktury.

    • Hej Damian!

      Używam to w jednym projekcie i na razie spisuje się dobrze (sam mechanizm, gorzej już z użyciem tego w Entity Framework, aby za jego pomocą wyciągać dane). Jeden kolega z biura również używa to w swoim projekcie (ale tylko zapis, bez używania tego w samej aplikacji) i też na razie na to nie narzeka.

      A w czym dokładnie miałeś problem? Myślę, że część odpowiedzi może pojawić się w kolejnym wpisie 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *