Jak korzystać z tabeli czasowej SQL Server

Tabele czasowe, znane również jako tabele systemowe, to tabele, które pozwalają śledzić i zachować historię danych w określonej tabeli. Korzystając z tabel czasowych, możesz śledzić historię zmian wprowadzonych w danych w tabeli.

Ten artykuł będzie podstawą do tworzenia, pracy i używania tabel czasowych w SQL Server.

Systemowe tabele zostały wprowadzone w standardzie ANSI SQL 2011 i były dostępne jako funkcja w SQL Server 2016 i powyżej.

W przeciwieństwie do normalnej tabeli, która może tylko pokazywać i pracować z bieżącymi danymi, tabele czasowe pozwalają przeglądać i pracować nawet z wcześniej usuniętymi danymi. Jak wspomniano, jest to możliwe ze względu na zdolność tabeli czasowej do śledzenia zmian wprowadzonych w danych w tabeli.

Tabela zawiera dwie kluczowe kolumny: SysstartTime i Sysendtime. Te dwie kolumny są używane do zdefiniowania istniejących i poprzednich danych dla każdego rekordu w tabeli. Możesz użyć określonych odstępów czasu, aby zobaczyć, jak zmieniły się dane w tabeli.

Utwórz tabelę czasową

Zanim utworzysz tabelę czasową, musi spełniać następujące wymagania:

1. Tabela czasowa musi zawierać zdefiniowane ograniczenie klucza podstawowego.
2. Musi zawierać dwie kolumny, aby zarejestrować datę początkową i końcową. Te kolumny muszą być typu danych DATETIME2. Kolumny powinny być zadeklarowane jako zawsze generowane jako start/koniec wiersza.
3. SQL Server zakłada, że ​​dwie kolumny nie są w wieku. Stąd instrukcja Utwórz tabelę nie powiada się, jeśli zapytanie próbuje ustawić kolumny, które są zerowe.
4. SQL Server automatycznie generuje tabelę historii przy użyciu podobnego schematu jak tabela czasowa.
5. Nie możesz użyć zamiast wyzwalaczy w tabeli wersji systemowej.
6. Tabela historii nie powinna zawierać żadnych ograniczeń.
7. Nie możesz zmienić danych w tabeli historii.
8. Instrukcje, takie jak wstawka i aktualizacja, nie mogą odwoływać się do kolumn z okresu.
9. Tabela historii jest tworzona jako tabela historii wiersza, a kompresja strony jest stosowana, jeśli dotyczy. W przeciwnym razie stół jest nieskompresowany.
10. SQL Server automatycznie generuje indeks klastrowy dla tabeli historii.

Jak utworzyć tabelę czasową: T-SQL

Spójrzmy na prostą demonstrację tworzenia stolika czasowego. Rozważ przykładowe zapytanie pokazane poniżej:

Utwórz tabelę DBO.my_temporal_table (
Id int,
fname varchar (50),
e -mail varchar (255),
Departament Varchar (50),
Ograniczenie klucz podstawowy (id),
SysStartTime DateTime2 generowane zawsze, ponieważ wiersz nie uruchamia się nie null,
Sysendtime DateTime2 wygenerowane zawsze jako rzęd,
Okres dla systemu System_time (SysStartTime, SysendTime)) z (System_versioning = on);

Po uruchomieniu powyższego zapytania serwer SQL utworzy tabelę o określonej nazwie.

W SQL Server Management Studio możesz wyświetlić tabelę w wersji systemowej, rozszerzając opcję tabel w docelowej bazie danych:

Zauważ, że SQL Server automatycznie generuje tabelę historii z podobnym schematem jak tabela wersji systemowej. Zwróć jednak uwagę na kolumny w tabeli historii. Zauważ, że nie mają żadnych ograniczeń.

Rozważ obraz pokazany poniżej:

Jak zobaczysz, SQL Server generuje tabelę historii pod nazwą po określonym formacie. Aby ustawić niestandardową nazwę tabeli historii, określ w instrukcji Utwórz tabelę, jak pokazano:

---
Okres dla systemu_time (SysStartTime, SysendTime)) z (System_versioning = on, History_Table = myTemporal_TableHistory);
---

Następnie, jeśli rozszerzysz opcję indeksu dla tabeli historii, zauważysz, że SQL Server automatycznie wygenerował indeks klastrowy:

Za pomocą tabel czasowych

Przetestujmy funkcjonalność tabel czasowych, wkładając kilka rekordów do tabeli. Rozważ przykładowe zapytanie pokazane poniżej:

Wstaw do my_temporal_table (id, fname, e -mail, dział)
Wartości (1, „John Davis”, „[email protected] ', „front-end”),
(2, „Ruby Raw”, „[email protected] ', „baza danych”),
(3, „Scott Turner”, „[email protected] ', „pełny stos”),
(4, „Alice Jensen”, „[email protected] ', „kontrola wersji”),
(5, „Peter Green”, „[email protected] ', „backend”);

Po wprowadzeniu przykładowych danych do tabeli możemy je zapytać jako:

Wybierz * z my_temporal_table;

Powinieneś uzyskać wyjście blisko tego pokazanego poniżej

Aby zrozumieć, jak działa tabela wersji systemowej, usuńmy i zaktualizujmy wiersze w tabeli:

Usuń z my_temporal_table, gdzie dział = „baza danych”;
Aktualizacja my_temporal_table Set fname = „John M” gdzie id = 5;

Następnie zapytaj dane w głównej tabeli:

Wybierz * z my_temporal_table;

Jeśli zapytasz o tabelę historii, powinieneś zobaczyć starą wersję danych z odpowiednimi znacznikami czasu.

Wniosek

Ten przewodnik obejmował koncepcję tabeli czasowej lub systemowej na serwerze SQL. Korzystając z tego przewodnika, będziesz w stanie śledzić historię danych za pomocą tabel czasowych SQL Server. Mamy nadzieję, że ten artykuł jest pomocny. Sprawdź więcej artykułów z Linux, aby uzyskać wskazówki i samouczki.