Udostępnij za pośrednictwem

Tworzenie tabeli czasowej w wersji systemowej

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) oraz nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w Microsoft Fabric

Istnieją trzy sposoby tworzenia tabeli czasowej w wersji systemowej podczas rozważania sposobu określenia tabeli historii:

  • Tabela czasowa z tabelą historii anonimowej : należy określić schemat bieżącej tabeli i umożliwić systemowi utworzenie odpowiedniej tabeli historii z automatycznie wygenerowaną nazwą.

  • Tabela czasowa z domyślną tabelą historii : należy określić nazwę schematu tabeli historii i nazwę tabeli, a system utworzy tabelę historii w tym schemacie.

  • Tabela czasowa z tabelą historii zdefiniowaną przez użytkownika utworzoną wcześniej: tworzysz tabelę historii, która odpowiada twoim potrzebom, a następnie odwołujesz się do tej tabeli podczas tworzenia tabeli czasowej.

Tworzenie tabeli czasowej z anonimową tabelą historii

Tworzenie tabeli czasowej z anonimową tabelą historii jest wygodną opcją szybkiego tworzenia obiektów, szczególnie w prototypach i środowiskach testowych. Jest to również najprostszy sposób tworzenia tabeli czasowej, ponieważ nie wymaga żadnego parametru w klauzuli SYSTEM_VERSIONING. W poniższym przykładzie zostanie utworzona nowa tabela z włączoną obsługą wersji systemu bez definiowania nazwy tabeli historii.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

Uwagi

Tabela czasowa z wersją systemu musi mieć zdefiniowany klucz podstawowy i mieć dokładnie jedną PERIOD FOR SYSTEM_TIME zdefiniowaną z dwiema kolumnami data/godzina/godzina2 zadeklarowaną jako GENERATED ALWAYS AS ROW START lub GENERATED ALWAYS AS ROW END.

Zakłada się, że kolumny PERIOD są zawsze nienullowalne, nawet jeśli nienullowalność nie jest określona. Jeśli kolumny PERIOD są jawnie zdefiniowane jako dopuszczane do wartości null, instrukcja CREATE TABLE kończy się niepowodzeniem.

Tabela historii musi być zawsze zgodna ze schematem z bieżącą lub czasową tabelą w odniesieniu do liczby kolumn, nazw kolumn, kolejności i typów danych.

Tabela historii anonimowej jest tworzona automatycznie w tym samym schemacie co bieżąca lub czasowa tabela.

Nazwa tabeli historii anonimowej ma następujący format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Sufiks jest opcjonalny i jest dodawany tylko wtedy, gdy pierwsza część nazwy tabeli nie jest unikatowa.

Tabela historii jest tworzona jako tabela wierszowa. PAGE kompresja jest stosowana, jeśli to możliwe, w przeciwnym razie tabela historii jest nieskompresowana. Na przykład niektóre konfiguracje tabeli, takie jak kolumny SPARSE, nie zezwalają na kompresję.

Domyślny indeks klastrowany jest tworzony dla tabeli historii z automatycznie wygenerowaną nazwą w formacie IX_<history_table_name>. Indeks klastrowany zawiera kolumny PERIOD (koniec, początek).

W Fabric SQL Database utworzona tabela historii nie jest odzwierciedlana w Fabric OneLake.

Aby utworzyć bieżącą tabelę jako tabelę zoptymalizowaną pod kątem pamięci, zobacz Tabele czasowe w wersji systemowej z tabelami zoptymalizowanymi pod kątem pamięci.

Tworzenie tabeli czasowej z domyślną tabelą historii

Tworzenie tabeli czasowej z domyślną tabelą historii jest wygodną opcją, jeśli chcesz kontrolować nazewnictwo i nadal polegaj na systemie w celu utworzenia tabeli historii z konfiguracją domyślną. W poniższym przykładzie zostanie utworzona nowa tabela z włączoną obsługą wersji systemu z jawnie zdefiniowaną nazwą tabeli historii.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Uwagi

Tabela historii jest tworzona przy użyciu tych samych reguł, które mają zastosowanie do tworzenia "anonimowej" tabeli historii, z następującymi regułami, które mają zastosowanie specjalnie do nazwanej tabeli historii.

  • Nazwa schematu jest obowiązkowa dla parametru HISTORY_TABLE.

  • Jeśli określony schemat nie istnieje, instrukcja CREATE TABLE zakończy się niepowodzeniem.

  • Jeśli tabela określona parametrem HISTORY_TABLE już istnieje, dokonuje weryfikacji względem nowo utworzonej tabeli czasowej pod względem spójności schematu i spójności danych czasowych. Jeśli określisz nieprawidłową tabelę historii, instrukcja CREATE TABLE zakończy się niepowodzeniem.

Tworzenie tabeli czasowej z tabelą historii zdefiniowanej przez użytkownika

Tworzenie tabeli czasowej z tabelą historii zdefiniowaną przez użytkownika jest wygodną opcją, gdy użytkownik chce określić tabelę historii z konkretnymi opcjami przechowywania i różnymi indeksami dostosowanymi do zapytań historycznych. W poniższym przykładzie tabela historii zdefiniowanej przez użytkownika jest tworzona ze schematem zgodnym z utworzoną tabelą czasową. W tej tabeli historii zdefiniowanej przez użytkownika tworzony jest indeks sklasteryzowanego magazynu kolumn oraz dodatkowy nieklastrowany indeks magazynu rzędów (B-tree) dla wyszukiwań punktowych. Po utworzeniu tej tabeli historii zdefiniowanej przez użytkownika zostanie utworzona tabela czasowa określająca tabelę historii zdefiniowanej przez użytkownika jako domyślną tabelę historii.

Notatka

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach przechowywania wierszy silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architekturę indeksu SQL Server i Azure SQL oraz przewodnik projektowania.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Uwagi

Jeśli planujesz uruchamiać zapytania analityczne na danych historycznych, które korzystają z funkcji agregacji lub funkcji okien, zdecydowanie zaleca się utworzenie klastrowanego magazynu kolumnowego jako indeksu podstawowego w celu poprawy kompresji i wydajności zapytań.

Jeśli planujesz używać tabel czasowych na potrzeby inspekcji danych (czyli wyszukiwanie historycznych zmian dla pojedynczego wiersza z bieżącej tabeli), należy utworzyć tabelę historii magazynu wierszy z indeksem klastrowanym.

Tabela historii nie może mieć klucza podstawowego, kluczy obcych, unikatowych indeksów, ograniczeń tabeli ani wyzwalaczy. Nie można go skonfigurować do przechwytywania danych o zmianach, śledzenia zmian, replikacji transakcyjnej ani replikacji złożeniowej.

W bazie danych SQL Fabric i w usłudze Azure SQL Database z skonfigurowanym mirrorowaniem Fabric, gdy podczas tworzenia tabeli czasowej używa się istniejącej tabeli jako tabeli historii, to ta istniejąca tabela przestaje być mirrorowana.

Zmień tabelę nietemporalną na systemową wersjonowaną tabelę czasową.

Można włączyć przechowywanie wersji systemu w istniejącej tabeli innej niż czasowa, na przykład w przypadku migrowania niestandardowego rozwiązania czasowego do wbudowanej obsługi.

Na przykład może istnieć zestaw tabel, w których obsługa wersji jest implementowana za pomocą wyzwalaczy. Korzystanie z tymczasowego przechowywania wersji systemu jest mniej złożone i zapewnia inne korzyści, w tym:

  • Niezmienna historia
  • Nowa składnia zapytań dotyczących podróży czasowych
  • Lepsza wydajność DML
  • Minimalne koszty konserwacji

Podczas konwertowania istniejącej tabeli rozważ użycie klauzuli HIDDEN, aby ukryć nowe kolumny PERIOD (kolumny datetime2ValidFrom i ValidTo), aby uniknąć wpływu na istniejące aplikacje, które nie określają jawnie nazw kolumn (na przykład SELECT * lub INSERT bez listy kolumn), które nie są przeznaczone do obsługi nowych kolumn.

Dodanie wersjonowania do tabel nietemporalnych

Jeśli chcesz rozpocząć śledzenie zmian dla tabeli nietemporalnej zawierającej dane, musisz dodać definicję PERIOD i opcjonalnie podać nazwę dla pustej tabeli historii, którą SQL Server utworzy dla Ciebie.

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Ważny

Precyzja DATETIME2 powinna odpowiadać precyzji dla tabeli bazowej.

Uwagi

Dodawanie kolumn, które nie mogą zawierać wartości null, z wartościami domyślnymi do istniejącej tabeli z danymi jest operacją obejmującą dane we wszystkich edycjach innych niż SQL Server Enterprise (na której jest to tylko operacja metadanych). W przypadku dużej istniejącej tabeli historii z danymi w wersji SQL Server Standard dodanie kolumny innej niż null może być kosztowną operacją.

Ograniczenia dla kolumn początku i zakończenia okresu muszą być starannie wybrane:

  • Wartość domyślna dla kolumny startowej określa, z którego punktu w czasie uważasz, że istniejące wiersze mają być prawidłowe. Nie można go określić jako punktu daty/godziny w przyszłości.

  • Godzina zakończenia musi być określona jako maksymalna wartość dla danej dokładności daty/godziny2, na przykład 9999-12-31 23:59:59 lub 9999-12-31 23:59:59.9999999.

Dodanie PERIOD przeprowadza sprawdzanie spójności danych w bieżącej tabeli, aby upewnić się, że istniejące wartości kolumn dotyczących okresu są prawidłowe.

Po określeniu istniejącej tabeli historii podczas włączania SYSTEM_VERSIONINGsprawdzanie spójności danych odbywa się zarówno w bieżącej tabeli, jak i w tabeli historii. Można go pominąć, jeśli określisz DATA_CONSISTENCY_CHECK = OFF jako dodatkowy parametr.

Migrowanie istniejących tabel do wbudowanej obsługi

W tym przykładzie pokazano, jak przeprowadzić migrację z istniejącego rozwiązania opartego na wyzwalaczach do wbudowanego wsparcia dla funkcji temporalnych. W tym przykładzie zakładamy, że bieżące rozwiązanie niestandardowe dzieli bieżące i historyczne dane na dwie oddzielne tabele użytkowników (ProjectTaskCurrent i ProjectTaskHistory).

Jeśli istniejące rozwiązanie używa pojedynczej tabeli do przechowywania rzeczywistych i historycznych wierszy, należy podzielić dane na dwie tabele przed krokami migracji przedstawionymi w poniższym przykładzie. Najpierw usuń trigger w przyszłej tabeli czasowej. Następnie upewnij się, że kolumny PERIOD nie są dopuszczane do wartości null.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Uwagi

Odwoływanie się do istniejących kolumn w definicji PERIOD niejawnie zmienia generated_always_type na AS_ROW_START i AS_ROW_END dla tych kolumn.

Dodanie PERIOD przeprowadza sprawdzanie spójności danych w bieżącej tabeli w celu upewnienia się, że istniejące wartości kolumn dotyczących okresu są prawidłowe.

Zdecydowanie zalecamy ustawienie SYSTEM_VERSIONING przy użyciu DATA_CONSISTENCY_CHECK = ON, aby wymusić kontrole spójności danych na istniejących danych.

Jeśli preferowane są ukryte kolumny, użyj polecenia ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Powiązana zawartość