Megosztás a következőn keresztül:

Temporális táblák

  • Cikk

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók Azure SQL DatabaseAzure SQL Kezelt PéldánySQL-adatbázis a Microsoft Fabric-ban

A temporális táblák (más néven rendszerverziójú temporális táblák) egy olyan adatbázis-szolgáltatás, amely beépített támogatást nyújt a táblában tárolt adatokról bármikor, és nem csak az aktuális időpontban helyes adatokról.

Rendszer által verziózott temporális táblák használatának első lépései, és tekintse át a temporális táblák használatának forgatókönyveit.

Mi az a rendszer-verziózott temporális tábla?

A rendszer által verziózott időbeli táblázat egy olyan felhasználói táblatípus, amely az adatváltozások teljes előzményeinek megőrzésére szolgál, és lehetővé teszi az egyszerű időpontalapú elemzést. Ezt a temporális táblázattípust rendszerverziójú időtáblának nevezzük, mivel a rendszer kezeli az egyes sorok (vagyis az adatbázismotor) érvényességi idejét.

Minden temporális tábla két explicit módon definiált oszlopból áll, mindegyik datetime2 adattípussal. Ezeket az oszlopokat periódus oszlopoknak nevezzük. Ezeket az időszakoszlopokat a rendszer kizárólag az egyes sorok érvényességi idejének rögzítésére használja, amikor egy sort módosít. Az aktuális adatokat tároló főtáblát az aktuális tábla, vagy egyszerűen csak az időbeli táblanevezik.

Ezen időszakoszlopok mellett egy időbeli tábla egy tükrözött sémával rendelkező másik táblára mutató hivatkozást is tartalmaz, az úgynevezett előzménytáblát. A rendszer az előzménytáblával automatikusan tárolja a sor előző verzióját minden alkalommal, amikor egy sor frissül vagy törlődik az időtáblában. A temporális tábla létrehozása során megadhat egy meglévő előzménytáblát (amelynek séma-kompatibilisnek kell lennie), vagy engedélyezheti, hogy a rendszer létrehozhasson egy alapértelmezett előzménytáblát.

Miért időleges?

A valós adatforrások dinamikusak, és az üzleti döntések gyakran olyan megállapításokra támaszkodnak, amelyeket az elemzők az adatfejlődésből kaphatnak. A temporális táblák használati esetei a következők:

  • Az adatváltozások naplózása és szükség esetén az adatelemzés elvégzése
  • Az adatok állapotának rekonstruálása a múltban bármikor
  • Trendek kiszámítása az idő függvényében
  • A döntéstámogatási alkalmazások lassan változó dimenziójának fenntartása
  • Véletlen adatváltozások és alkalmazáshibák helyreállítása

Hogyan működik az időbeli munka?

A táblák rendszerverzió-használatát táblapárként implementáljuk: egy aktuális táblát és egy előzménytáblát. Ezen táblákon belül két további datetime2 oszlop határozza meg az egyes sorok érvényességi idejét:

  • Időszak kezdő oszlopa: A rendszer itt rögzíti a sor kezdési időpontját, amely általában ValidFrom oszlopként van jelölve.

  • Időszak vége oszlop: A rendszer az adott sor befejezési idejét rögzíti ebben az oszlopban, amelyet általában ValidTo oszlopként jelölnek.

Az aktuális táblázat az egyes sorokhoz aktuális értéket tartalmazza. Az előzménytáblában minden sor minden korábbi értéke (régi verzió) szerepel, ha vannak ilyenek, és annak az időszaknak a kezdési és befejezési idejét, amelyre érvényes volt.

Időbeli táblázat működését bemutató diagram.

Az alábbi szkript az alkalmazottak adatait tartalmazó forgatókönyvet mutatja be:

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

További információ: Rendszerverziójú temporális tábla létrehozása.

  • Beszúrások: A rendszer a ValidFrom oszlop értékét az aktuális tranzakció kezdő időpontjára állítja (az UTC időzónában) a rendszer óra alapján, és a ValidTo oszlop értékét a 9999-12-31maximális értékéhez rendeli. Ez nyitottként jelöli meg a sort.

  • Frissítések: A rendszer az előzménytáblában tárolja a sor előző értékét, és a ValidTo oszlop értékét az aktuális tranzakció kezdő időpontjára állítja (az UTC időzónában) a rendszeróra alapján. Ez lezártként jelöli meg a sort, és egy olyan pontot rögzít, amelyre a sor érvényes volt. Az aktuális táblázatban a sor frissül az új értékével, és a rendszer a rendszer óra alapján beállítja a ValidFrom oszlop értékét a tranzakció kezdési időpontjára (az UTC időzónában). A ValidTo oszlop aktuális táblázatának frissített sorának értéke továbbra is a 9999-12-31maximális értéke marad.

  • Törlés: A rendszer a sor előző értékét tárolja az előzménytáblában, és a ValidTo oszlop értékét az aktuális tranzakció kezdő időpontjára állítja (az UTC időzónában) a rendszeróra alapján. Ez lezártként jelöli meg a sort, és egy olyan időszakot rögzít, amelyre az előző sor érvényes volt. Az aktuális táblában a sor el lesz távolítva. Az aktuális tábla lekérdezései nem ezt a sort adják vissza. Csak az előzményadatokkal foglalkozó lekérdezések a sorokat lezáró adatokat ad vissza.

  • Egyesítés: A művelet pontosan úgy viselkedik, mintha legfeljebb három utasítást (egy INSERT, egy UPDATEés/vagy egy DELETE) hajtanak végre, attól függően, hogy mi van megadva a MERGE utasítás műveleteiként.

A rendszerben datetime2 oszlopokban rögzített időpontok a tranzakció kezdő időpontján alapulnak. Az egyetlen tranzakcióban beszúrt összes sor például az SYSTEM_TIME időszak kezdetének megfelelő oszlopban rögzített UTC-idővel rendelkezik.

Ha adatmódosítási lekérdezéseket futtat egy időbeli táblán, az adatbázismotor hozzáad egy sort az előzménytáblához, még akkor is, ha az oszlopértékek nem változnak.

Hogyan kérdezhetem le az időbeli adatokat?

A SELECT ... FROM <table> utasítás új záradékkal rendelkezik FOR SYSTEM_TIME, amely öt időspecifikus alklámot tartalmaz az aktuális és az előzménytáblák adatainak lekérdezéséhez. Ez az új SELECT utasításszintaxis közvetlenül egyetlen táblán támogatott, több összekapcsoláson keresztül terjed, valamint több időbeli táblán alapuló nézeteken keresztül.

Ha a FOR SYSTEM_TIME záradékot az öt alzáradék egyikét használva hajt végre lekérdezést, a rendszer tartalmazza a időbeli táblából származó előzményadatokat, ahogyan az az alábbi ábrán látható.

a temporális lekérdezés működését bemutató diagram.

Az alábbi lekérdezés olyan alkalmazott sorverziókat keres a WHERE EmployeeID = 1000 szűrőfeltétel alapján, amelyek legalább részben aktívak voltak a 2021. január 1. és 2022. január 1. közötti időszakban (beleértve a felső határt is).

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 kiszűri azokat a sorokat, amelyek érvényességi ideje nulla (ValidFrom = ValidTo).

Ezek a sorok akkor jönnek létre, ha ugyanazon a tranzakción belül több frissítést hajt végre ugyanazon az elsődleges kulcson. Ebben az esetben az időbeli lekérdezés csak a tranzakciók előtti sorverziókat, a tranzakciók utáni aktuális sorokat adja vissza.

Ha ezeket a sorokat bele kell foglalnia az elemzésbe, közvetlenül kérdezheti le az előzménytáblát.

A következő táblázatban ValidFrom a Minősítő sorok oszlopban a lekérdezett tábla ValidFrom oszlopának értékét, ValidTo pedig a lekérdezett tábla ValidTo oszlopában lévő értéket jelöli. A teljes szintaxist és példákat lásd a FROM záradékban, valamint a JOIN, APPLY, PIVOT, és a Rendszerverziózású temporális táblák lekérdezésecímű részben.

Kifejezés Kvalifikáló sorok Jegyzet
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Olyan táblázatot ad vissza, amely a múltban megadott időpontban aktuális értékeket tartalmazó sorokat tartalmaz. Belsőleg egy egyesítést hajtunk végre az időbeli tábla és az előzménytáblája között. A rendszer szűri az eredményeket, hogy az date_time paraméter által megadott időpontban érvényes sorok értékeit adja vissza. A sor értéke akkor tekinthető érvényesnek, ha a system_start_time_column_name érték kisebb vagy egyenlő a date_time paraméter értékénél, és a system_end_time_column_name érték nagyobb, mint a date_time paraméter értéke.
FROM start_date_timeTOend_date_time ValidFrom < befejezési_dátum_és_időAND ValidTo >kezdési_dátum_és_idő A megadott időtartományon belül aktív összes sorverzió értékeit tartalmazó táblát ad vissza, függetlenül attól, hogy az FROM argumentum start_date_time paraméterértéke előtt kezdtek aktívak lenni, vagy a TO argumentum end_date_time paraméterértéke után már nem voltak aktívak. Belsőleg egy egyesítést hajtunk végre az időbeli tábla és az előzménytáblája között. A rendszer szűri az eredményeket, hogy visszaadja az összes olyan sorverzió értékeit, amelyek a megadott időtartomány alatt bármikor aktívak voltak. A FROM végpont által meghatározott alsó határon nem aktív sorok nem szerepelnek, és a TO végpont által meghatározott felső határon aktívvá vált rekordok szintén nem lesznek belefoglalva.
BETWEEN start_date_timeANDend_date_time ValidFrom <= end_date_timeAND ValidTo >start_date_time A FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time leírásában szereplővel megegyezik, kivéve, hogy a visszaadott sorok táblázata tartalmazza azokat a sorokat, amelyek a end_date_time végpont által meghatározott felső határon lettek aktívvá.
CONTAINED IN (kezdési_dátum_idő, befejezési_dátum_idő) ValidFrom >= kezdési_időpontAND ValidTo <=zárási_időpont Egy olyan táblát ad vissza, amely az összes olyan sorverzió értékeit tartalmazza, amelyek a CONTAINED IN argumentum két időszakértékével meghatározott megadott időtartományon belül lettek megnyitva és bezárva. Azokat a sorokat is belefoglaljuk, amelyek pontosan az alsó határon váltak aktívvá, vagy a felső határon már nem aktívak.
ALL Minden sor Az aktuális és az előzménytáblához tartozó sorok egyesítését adja vissza.

Az időszakoszlopok elrejtése

Elrejtheti az időszakoszlopokat, így a kifejezetten nem hivatkozó lekérdezések nem adják vissza ezeket az oszlopokat (például SELECT * FROM <table>futtatásakor).

Rejtett oszlop visszaadásához explicit módon hivatkoznia kell a lekérdezés rejtett oszlopára. Hasonlóképpen a(z) INSERT és BULK INSERT utasítások úgy folytatódnak, mintha ezek az új időszakoszlopok nem lennének jelen (és az oszlopértékek automatikusan kitöltődnek).

A HIDDEN záradék használatáról további információt CREATE TABLE és ALTER TABLEcímű cikkben talál.

Minták

  • ASP.NET: Tekintse meg a ASP.NET Core webalkalmazást, hogy megtanulja, hogyan hozhat létre időalapú alkalmazást időbeli táblák használatával.

  • AdventureWorks mintaadatbázis: Töltse le az SQL Server AdventureWorks-adatbázisát, amely tartalmazza a temporális táblafunkciókat.

Kapcsolódó tartalom