Hamarosan megjelenik a publikus CTP is az SQL 2016-ból, de addig is lássunk pár újdonságot részletesen is. Ebben a bejegyzésben a Temporal Table funkcióról lesz szó.
FONTOS: az itt leírtak a publikus és/vagy az RTM verzióban nem vagy másképpen működhetnek!
Mi is az a Temporal Table?
Ez egy érdekes megközelítése egy tábla adott időpontra való visszaállásának, akár úgy is mondhatnám, hogy egyfajta audit tábla is lehetne: gyakorlatilag arról van szó, hogy minden változtatást,a mit egy adott tábla sorain elvégzek, készít róla egy mentést és megjelöli, hogy ez mikor történt.
Létrehozás
Lássunk egy példát:
create database TemporalTableTest
go
use TemporalTableTest
go
create table t1
(
Col1 int identity primary key,
Col2 char(1) default 'A',
ValidStartDate datetime2 generated always as row start not null,
ValidEndDate datetime2 generated always as row end not null,
PERIOD FOR SYSTEM_TIME (ValidStartDate,ValidEndDate)
) with (system_versioning = on);
go
Ezzel a kóddal egy új táblát hoztam létre, amire már be is állítottam az "auditálást", ahogy ez alább látszik is:
Ez így tök jó, mert új táblánál előre meg tudom adni, hogy akarok egy ilyen megoldást, de mi van a meglévő tábláimmal? Azokkal is lehet ilyet? Természetesen igen, lássuk is, hogy mi szükséges ehhez, illetve ezen keresztül magyarázatot is adok a fenti kódra is.
Első körben létrehozok egy T2 táblát:
create table t2
(
Col1 int identity primary key,
Col2 char(1) default 'A'
)
Ez a T2 tábla lesz, amit majd "auditálni szeretnék, ehhez pedig készítek egy T2_History táblát, de már egy másik filegroup-ra!
create table t2_history
(
Col1 int not null,
Col2 char(1) default 'A',
ValidStartDate datetime2 not null,
ValidEndDate datetime2 not null
) ON FG_AuditData
A t2_history táblában vegyünk észre két sort:
- ValidStartDate: ez határozza meg a sor érvényességének kezdetét, datetime2 típus és UTC dátum/idő kerül ide.
- ValidEndDate: ez határozza meg a sor érvényességének végét, datetime2 típus és UTC dátum/idő kerül ide.
Ezekre a t2 táblánkban is szükség lesz. Így vegyük ezeket is fel ide:
alter table t2
add ValidStartDate datetime2 generated always as row start not null,
ValidEndDate datetime2 generated always as row end not null,
PERIOD FOR SYSTEM_TIME (ValidStartDate,ValidEndDate)
Ezek után már be is kapcsolhatjuk, hogy a t2 tábla a t2_history táblát használja az auditálásra:
alter table t2
set (system_versioning = on (history_table = dbo.t2_history))
Metaadatok
Ezeket a táblákat a sys.tables DMV-ből lehet lekérdezni, már létezik erre oszlop, nem is egy:
- temporal_type: 0,1,2 értékeket vehet fel.
- temporal_type_desc: ez a NON_TEMPORAL_TABLE értéket veszi fel, ha sima tábla, HISTORY_TABLE érték, ha manuálisan adom meg a "history" táblát, illetve automatikus esetben a SYSTEM_VERSIONED_TEMPORAL_TABLE.
- history_table_id: ez a tábla object_id értéke.
Az alábbi lekérdezéssel meg lehet nézni az adatbázisban található összes temporal table-t:
SELECT T1.name as TemporalTableName,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T2.name as HistoryTableName, SCHEMA_NAME(T2.schema_id)
AS HistoryTableSchema,
T1.temporal_type_desc
FROM sys.tables T1
LEFT JOIN sys.tables T2
ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type <> 0
ORDER BY T1.history_table_id DESC
Lekérdezés
Az alábbi egyszerű lekérdezéssel meg lehet nézni, hogy egy adott időintervallum során milyen adataim voltak/vannak, illetve ezek közül melyek az éppen aktuálisak:
select
Col1,
Col2,
ValidStartDate,
ValidEndDate,
iif(year(ValidEndDate) = 9999, 1, 0) as IsActual
from t2
for system_time between '2015.01.01' and '2015.12.31'
Korlátozások/megkötések
Az alábbi korlátozások állnak jelenleg fent:
- A "history" tábla oszlopainak meg kell egyeznie típus és név szerint az eredeti tábla oszlopaival,
- nem tartalmazhat triggert, unique, foreign key vagy bármilyen egyéb megszorítást (constrain-t) a "history" tábla,
- nem lehet filstream sem,
- a "history" táblában végzett módosítások csak akkor végezhetőek el, ha ki van kapcsolva ez a szolgáltatás,
- az eredeti táblában kell legyen primary key!
- manuális "history" tábla esetén kötelező megadni a 2 part nevet, azaz schema + tábla név a HISTORY_TABLE opciónál.
- INSERT műveletre NEM működik, ahhoz az eredeti táblát kell felhasználni!!!
Konklúzió
Maga a megoldás nem is rossz, azonban tényleges audithoz csak akkor lehet használni, ha a tábla tartalmazza a változtatást végző felhasználó nevét is egy oszlopban. Ezzel gyakorlatilag az un. "shadow table" auditálás valósult meg trigger felhasználása nélkül. Nagyon sok adat összegyűlhet az ilyen típusú táblákban, így azok particionálása igen valószínű, illetve az archiválás is egy partition switch művelettel valósítható meg a leghatékonyabban. Ajánlott a "history" tábla manuális létrehozása több okból is:
- A nevét az automatikus esetben nem tudom meghatározni, az mindig MSSQL_TemporalHistoryFor_object_id lesz
- Particionálás esetén is csak a manuális létrehozás során tudom megadni a CREATE TABLE során, hogy milyen PARTITION SCHEME legyen.
- Manuális létrehozás esetén meg tudom határozni, hogy mely filegroup-ra tegyem az egész táblát.
Ami nagyon jó: lehet indexeket és statisztikákat is tenni ezekre a táblákra is, mint a "rendes" táblákra.
Kicsit emlékeztet annak a megoldásnak bevezetésére, amit úgy kb 4-5 éve írtam egy nagyvállalat adatbázis auditálásához ;)
Frissítve: 2015.05.24. 2145 - kód javítás + lekérdezés hozzáadás