Tworzenie tabeli czasowej w wersji systemowej

Dotyczy do: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Możesz stworzyć systemowo wersjonowaną tabelę czasową na trzy sposoby, w zależności od tego, jak określisz tabelę historii:

  • Tabela czasowa z anonimową tabelą historii: określasz schemat bieżącej tabeli i pozwalasz systemowi utworzyć odpowiadającą mu tabelę 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. To także najprostszy sposób na stworzenie tabeli czasowej, ponieważ nie wymaga żadnego parametru w klauzuli SYSTEM_VERSIONING . Poniższy przykład tworzy nową tabelę z włączonym wersjonowaniem systemowym, 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
);

Remarks

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.

Database Engine automatycznie tworzy anonimową tabelę historii w tym samym schemacie co bieżąca lub temporalna 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 klasterizowany indeks jest tworzony dla tabeli historii z automatycznie generowaną 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ą. Poniższy przykład tworzy nową tabelę z włączonym wersjonowaniem systemowym, z wyraźnie 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
    )
);

Remarks

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 chcesz określić tabelę historii z określonymi opcjami przechowywania i różnymi indeksami dostosowanymi do zapytań historycznych. W poniższym przykładzie tworzysz tabelę historii zdefiniowaną przez użytkownika, której schemat jest zgodny z tabelą czasową. Ta tabela historii zawiera klastrowany indeks magazynu kolumnowego oraz dodatkowy nieklastrowany indeks magazynu wierszowego (B-tree) do wyszukiwań punktowych. Po utworzeniu tabeli historii tworzysz tabelę czasową i określasz tabelę historii zdefiniowaną przez użytkownika jako domyślną tabelę historii.

Uwaga / Notatka

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach typu rowstore 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ę i przewodnik projektowania indeksu SQL Server i Azure SQL.

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
    )
);

Remarks

Jeśli planujesz uruchamiać zapytania analityczne na danych historycznych wykorzystujących agregaty lub funkcje okienkowe, zdecydowanie zaleca się utworzenie klastrowego magazynu kolumn jako indeksu głównego dla 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żesz włączyć wersjonowanie systemu na istniejącej tabeli nietemporalnej, na przykład gdy chcesz przenieść niestandardowe rozwiązanie temporalne na wbudowane wsparcie.

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 konwersji istniejącej tabeli rozważ użycie klauzuli HIDDEN do ukrycia nowych PERIOD kolumn (kolumny ValidFromdatetime2 i ValidTo), aby nie wpływać na istniejące aplikacje, SELECT *INSERT które nie określają wyraźnie nazw kolumn (na przykład lub nie mają listy kolumn) i nie są zaprojektowane 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

Important

Precyzja DATETIME2 powinna odpowiadać precyzji dla tabeli bazowej.

Remarks

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 elementu PERIOD powoduje wykonanie sprawdzenia spójności danych w bieżącej tabeli, aby upewnić się, że istniejące wartości w kolumnach okresowych 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. Ten przykład zakłada, że obecne niestandardowe rozwiązanie dzieli dane bieżące i historyczne na dwie oddzielne tabele użytkownika (ProjectTaskCurrent oraz ProjectTaskHistory).

Jeśli Twoje obecne rozwiązanie używa jednej tabeli do przechowywania rzeczywistych i historycznych wierszy, powinieneś podzielić dane na dwie tabele przed krokami migracji pokazanymi na następującym 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 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
        )
    );

Remarks

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 elementu PERIOD powoduje wykonanie sprawdzenia spójności danych w bieżącej tabeli, aby upewnić się, że istniejące wartości w kolumnach 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 następującego polecenia:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Treści powiązane

  • Last updated on