Udostępnij za pośrednictwem

Tabele czasowe

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) oraz nowsze wersje Azure SQL DatabaseAzure SQL Managed Instancebaza danych SQL w usłudze Microsoft Fabric

Tabele czasowe (znane również jako tabele czasowe w wersji systemowej) to funkcja bazy danych, która zapewnia wbudowaną obsługę dostarczania informacji o danych przechowywanych w tabeli w dowolnym momencie, a nie tylko danych, które są poprawne w bieżącym momencie w czasie.

Rozpocznij pracę z tabelami czasowymi w wersji systemoweji zapoznaj się ze scenariuszami użycia tabel czasowych .

Co to jest tabela czasowa z wersjonowaniem systemowym?

Tabela wersjonowana przez system jest typem tabeli użytkownika zaprojektowanym do zachowania pełnej historii zmian danych, co umożliwia łatwą analizę w dowolnym punkcie w czasie. Ten typ tabeli czasowej jest określany jako tabela czasowa w wersji systemowej, ponieważ system zarządza okresem ważności dla każdego wiersza (czyli aparatu bazy danych).

Każda tabela czasowa ma dwie jawnie zdefiniowane kolumny, z których każda ma data/godzina2 typ danych. Te kolumny są określane jako kolumny okresu. Te kolumny okresu są używane wyłącznie przez system do rejestrowania okresu ważności dla każdego wiersza, za każdym razem, gdy wiersz jest modyfikowany. Tabela główna przechowującą bieżące dane jest nazywana bieżącą tabeląlub po prostu jako tabela czasowa .

Oprócz tych kolumn okresów tabela czasowa zawiera również odwołanie do innej tabeli z odzwierciedlonym schematem, nazywanej tabelą historii . System używa tabeli historii do automatycznego przechowywania poprzedniej wersji wiersza za każdym razem, gdy wiersz w tabeli czasowej zostanie zaktualizowany lub usunięty. Podczas tworzenia tabeli czasowej można określić istniejącą tabelę historii (która musi być zgodna ze schematem) lub pozwolić systemowi utworzyć domyślną tabelę historii.

Dlaczego czasowe?

Rzeczywiste źródła danych są dynamiczne i częściej niż nie decyzje biznesowe opierają się na szczegółowych informacjach, które analitycy mogą uzyskać od ewolucji danych. Przypadki użycia tabel czasowych obejmują:

  • Przeprowadzanie inspekcji wszystkich zmian danych i przeprowadzanie analiz kryminalistycznych danych w razie potrzeby
  • Rekonstruowanie stanu danych w dowolnym momencie w przeszłości
  • Obliczanie trendów w czasie
  • Utrzymywanie wolno zmieniającego się wymiaru dla aplikacji obsługujących decyzje
  • Odzyskiwanie po przypadkowych zmianach danych i błędach aplikacji

Jak działa praca czasowa?

Versionowanie systemowe tabeli jest realizowane poprzez parę tabel: tabelę bieżącą i tabelę historii. W każdej z tych tabel dwie dodatkowe kolumny datetime2 służą do definiowania okresu ważności dla każdego wiersza:

  • kolumna początkowa okresu: system rejestruje czas rozpoczęcia wiersza w tej kolumnie, zazwyczaj oznaczany jako kolumna ValidFrom.

  • kolumna końcowa okresu: system rejestruje czas zakończenia wiersza w tej kolumnie, zazwyczaj oznaczany jako kolumna ValidTo.

Bieżąca tabela zawiera bieżącą wartość dla każdego wiersza. Tabela historii zawiera każdą poprzednią wartość (starej wersji) dla każdego wiersza, jeśli istnieje, oraz godzinę rozpoczęcia i godziny zakończenia okresu, dla którego była prawidłowa.

Diagram przedstawiający sposób działania tabeli czasowej.

Poniższy skrypt ilustruje scenariusz z informacjami o pracownikach:

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Aby uzyskać więcej informacji, zobacz Tworzenie wersjonowanej systemowo tabeli czasowej.

  • Wstawia: System ustawia wartość kolumny ValidFrom na godzinę rozpoczęcia bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego i przypisuje wartość kolumny ValidTo do maksymalnej wartości 9999-12-31. Oznacza to wiersz jako otwarty.

  • Aktualizacje: System przechowuje poprzednią wartość wiersza w tabeli historii i ustawia wartość kolumny ValidTo na początek bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Oznacza to wiersz jako zamknięty, z okresem, w którym wiersz był prawidłowy. W bieżącej tabeli wiersz jest aktualizowany przy użyciu nowej wartości, a system ustawia wartość kolumny ValidFrom na godzinę rozpoczęcia transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Wartość zaktualizowanego wiersza w bieżącej tabeli dla kolumny ValidTo pozostaje maksymalną wartością 9999-12-31.

  • Usuwa: System przechowuje poprzednią wartość wiersza w tabeli historii i ustawia wartość kolumny ValidTo na godzinę rozpoczęcia bieżącej transakcji (w strefie czasowej UTC) na podstawie zegara systemowego. Oznacza to wiersz jako zamknięty, z zarejestrowanym okresem, w którym poprzedni wiersz był prawidłowy. W bieżącej tabeli wiersz zostanie usunięty. Zapytania bieżącej tabeli nie zwracają tego wiersza. Tylko zapytania dotyczące danych historycznych zwracają dane, gdzie wiersz jest zamknięty.

  • Merge: Operacja zachowuje się dokładnie tak, jak gdyby maksymalnie trzy instrukcje (INSERT, UPDATEi/lub DELETE) były wykonywane, w zależności od tego, co jest określone jako akcje w instrukcji MERGE.

Czasy zarejestrowane w systemowych kolumnach datetime2 są oparte na podstawie godziny rozpoczęcia samej transakcji. Na przykład wszystkie wiersze wstawione w ramach jednej transakcji mają ten sam czas UTC zarejestrowany w kolumnie odpowiadającej początku okresu SYSTEM_TIME.

Po uruchomieniu zapytań modyfikacji danych w tabeli czasowej aparat bazy danych dodaje wiersz do tabeli historii, nawet jeśli żadne wartości kolumn nie ulegają zmianie.

Jak wykonywać zapytania dotyczące danych czasowych?

Instrukcja SELECT ... FROM <table> zawiera nową klauzulę FOR SYSTEM_TIME, z pięcioma podklasami specyficznymi dla czasu w celu wykonywania zapytań o dane w tabelach bieżących i historycznych. Ta nowa składnia instrukcji SELECT jest obsługiwana bezpośrednio w pojedynczej tabeli, propagowana za pomocą wielu sprzężeń i widoków na podstawie wielu tabel czasowych.

Podczas wykonywania zapytań przy użyciu klauzuli FOR SYSTEM_TIME przy użyciu jednej z pięciu podklas, dane historyczne z tabeli czasowej są uwzględniane, jak pokazano na poniższej ilustracji.

Diagram przedstawiający sposób działania zapytań czasowych.

Następujące zapytanie wyszukuje wersje wierszy dla pracownika z warunkiem filtru WHERE EmployeeID = 1000, które były aktywne co najmniej przez część okresu pomiędzy 1 stycznia 2021 r. a 1 stycznia 2022 r. (w tym górną granicę):

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME filtruje wiersze, które mają okres ważności o zerowym czasie trwania (ValidFrom = ValidTo).

Te wiersze są generowane, jeśli wykonujesz wiele aktualizacji na tym samym kluczu podstawowym w ramach tej samej transakcji. W takim przypadku wykonywanie zapytań czasowych zwraca tylko wersje wierszy przed transakcjami i bieżące wiersze po transakcjach.

Jeśli chcesz uwzględnić te wiersze w analizie, wykonaj zapytanie bezpośrednio w tabeli historii.

W poniższej tabeli ValidFrom w kolumnie "Kwalifikujące wiersze" reprezentuje wartość w kolumnie ValidFrom tabeli, którą przeszukujemy, a ValidTo reprezentuje wartość w kolumnie ValidTo tej tabeli. Aby uzyskać pełną składnię i przykłady, zobacz klauzuli FROM oraz JOIN, APPLY, PIVOTi Zapytaj dane w tabeli czasowej z wersjonowaniem systemowym.

Wyrażenie Kwalifikowanie wierszy Notatka
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Zwraca tabelę zawierającą wiersze zawierające wartości, które były bieżące w określonym punkcie w czasie w przeszłości. Wewnętrznie jest wykonywane połączenie między tabelą czasową a tabelą historii. Wyniki są filtrowane w celu zwrócenia wartości w wierszu, który był prawidłowy w czasie określony przez parametr date_time. Wartość wiersza jest uważana za prawidłową, jeśli wartość system_start_time_column_name jest mniejsza lub równa wartości parametru date_time, a wartość system_end_time_column_name jest większa niż wartość parametru date_time.
FROM start_date_timeTOend_date_time ValidFrom < czas_zakończeniaAND ValidTo >czas_rozpoczęcia Zwraca tabelę z wartościami dla wszystkich wersji wierszy, które były aktywne w określonym zakresie czasu, niezależnie od tego, czy zaczęły być aktywne przed wartością parametru start_date_time dla argumentu FROM lub przestały być aktywne po wartości parametru end_date_time dla argumentu TO. Wewnętrznie jest wykonywane połączenie między tabelą czasową a tabelą historii. Wyniki są filtrowane w celu zwrócenia wartości dla wszystkich wersji wierszy, które były aktywne w dowolnym momencie w określonym zakresie czasu. Wiersze, które przestały być aktywne dokładnie na dolnej granicy zdefiniowanej przez punkt końcowy FROM, nie są uwzględniane, a rekordy, które stały się aktywne dokładnie na górnej granicy zdefiniowanej przez punkt końcowy TO, również nie są uwzględniane.
BETWEEN start_date_timeANDend_date_time ValidFrom <= end_date_timeAND ValidTo >start_date_time Tak samo jak w opisie FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time, z wyjątkiem zwracanej tabeli wierszy zawiera wiersze, które stały się aktywne na górnej granicy zdefiniowanej przez punkt końcowy end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_timeAND ValidTo <=end_date_time Zwraca tabelę z wartościami dla wszystkich wersji wierszy, które zostały otwarte i zamknięte w określonym zakresie czasu zdefiniowanym przez dwie wartości okresu dla argumentu CONTAINED IN. Uwzględniane są wiersze, które stały się aktywne dokładnie na dolnej granicy lub przestały być aktywne dokładnie na górnej granicy.
ALL Wszystkie wiersze Zwraca połączenie wierszy należących do bieżącej i tabeli historii.

Ukryj kolumny okresu

Możesz wybrać ukrycie kolumn okresu, tak aby zapytania, które nie odwołują się do nich jawnie, nie zwracały tych kolumn (na przykład podczas uruchamiania SELECT * FROM <table>).

Aby zwrócić ukrytą kolumnę, należy jawnie odwołać się do ukrytej kolumny w zapytaniu. Podobnie wyrażenia INSERT i BULK INSERT są kontynuowane, jakby te nowe kolumny okresu nie istniały (a wartości kolumn są automatycznie wypełniane).

Aby uzyskać szczegółowe informacje na temat używania klauzuli HIDDEN, zobacz CREATE TABLE i ALTER TABLE.

Próbki

  • ASP.NET: zobacz aplikacji internetowej ASP.NET Core, aby dowiedzieć się, jak utworzyć aplikację czasową przy użyciu tabel czasowych.

  • przykładowa baza danych AdventureWorks: pobierz bazę danych AdventureWorks dla SQL Server, która zawiera funkcje tabel czasowych.

Powiązana zawartość