Förändringsspårning för SQL Server är en flexibel och lättanvänd teknik för övervakning av tabeller för inlägg, uppdateringar och raderingar. I det här inlägget diskuterar jag att komma igång med förändringsspårning i SQL Server och visar ett exempel på hur man kommer igång med det.
ändra spårning i SQL Server
ändra spårning är en lätt mekanism för att spåra vilka rader har infogats, uppdateras och raderas i tabeller övervakas av ändra spårning. Ändra spårning först dök upp i SQL Server 2008 och har varit i varje version sedan. Ännu bättre, ändra spårning finns i varje utgåva av SQL Server, även free express edition.
i ett nötskal, så här fungerar det: I tabeller som har ändringsspårning aktiverad spåras nettoändringen till varje rad internt och är tillgänglig via ändringsspårningsfunktioner. Varje ändring har ett versions-ID kopplat till det, och det kommer att finnas ett nytt versions-ID för varje rad som infogas, uppdateras eller raderas. Förändringsspårningsversionen är ett 8-byte heltal (BIGINT) som återspeglar det senaste ändrings-ID i den databasen. Det är viktigt att notera att förändringsspårningsversionen inte är tabellspecifik – en DML-operation i någon spårad tabell i varje databas ökar versionsnumret. Versionsnumret kommer alltid att vara sekventiellt, men kommer inte nödvändigtvis att vara sammanhängande inom en enda tabell om det finns mer än en tabell aktiverad för ändringsspårning.
ändra spårning är aktiverad på databasnivå. Därefter måste varje tabell som ska övervakas individuellt anlitas i ändringsspårning. Varje tabell som ska övervakas med ändringsspårning måste ha en primärnyckel, eftersom det här är radnivåidentifieraren som används för att rapportera om DML-operationer inom ändringsspårning. När du aktiverar ändringsspårning på tabellnivå kan du välja att spåra kolumnen / kolumnerna som ändrats i den senaste uppdateringen, vilket ger dig större insyn i vad som ändrats.
när du har konfigurerat är det en relativt enkel process att använda ändringsspårning. Det finns några funktioner – framför allt CHANGE_TRACKING_CURRENT_VERSION () och CHANGETABLE (), som kan användas för att kontrollera den aktuella versionen stämpel i förändring spårning och hämta listan över de senaste ändringarna. Jag ska visa båda dessa funktioner inom kort.
Förändringsspårning är inte Granskningsloggning
jag ska vara försiktig så att jag inte använder orden revision eller loggning för att beskriva ändringsspårning. Låt mig vara tydlig: detta är inte en fullständig loggningsmekanism. Ändringshistoriken spåras inte alls-ändringsspårning rapporterar bara det faktum att en ändring inträffade, men behåller inte versionshistoriken. Tänk på fallet med en rad data med ett ID på 1234. Den raden infogas, uppdateras sedan 5 gånger och raderas sedan. Ändra spårning skulle inte visa historiken för infoga, uppdatera och ta bort; snarare skulle det bara rapportera nettoförändringen, att rad-ID 1234 raderades. Om din laddningsprocess kräver detaljerad loggningshistorik för varje ändring (snarare än bara deltaet för alla ändringar), måste du använda något som change data capture.
ställa in Ändringsspårning i SQL Server
aktivera spårning på tabellnivå är en tvåstegsprocess. Först måste det vara aktiverat i databasen. Detta kan göras via användargränssnittet i Databasegenskaperna på fliken Ändra spårning.
som visas finns det inte mycket att konfigurera när du aktiverar ändringsspårning i en databas. Ställ bara in värdet för Ändringsspårning till True för att ställa in ändringsspårning för den databasen. Eventuellt kan Retentionsperiodens värde också justeras. Standardvärdet är 2 dagar, vilket jag har åsidosatt i det här exemplet för att använda 14 dagar istället. Som med de flesta UI-operationer finns det ett T-SQL-kommando för att göra samma sak. Kommandot för att ställa in ändringsspårning i den här databasen finns nedan.
efter detta steg är ändringsspårning aktiverad, men det spårar ännu inte något. Det måste fortfarande vara aktiverat för att varje tabell ska spåras. Tabellen egenskaper UI gör detta mycket enkelt.
som visas kan du helt enkelt ändra Ändringsspårningsvärdet till True för att ändra spårning för den här tabellen. I det här exemplet valde jag också att spåra kolumnerna som ändrats under Uppdateringar (mer om detta i lite).
det sista steget ovan skulle upprepas för varje tabell som ska spåras i förändringsspårning. När ändringsspårning har aktiverats lagras alla ändringar (infogar, uppdateringar eller raderar) i den tabellen i cachen för ändringsspårning.
ställa in Ändringsspårning
för ovanstående exempel ska jag infoga, uppdatera och ta bort vissa data för att visa hur man får tillgång till ändringsspårningsdata som genereras för dessa DML-operationer. Som referens är här tabellstrukturen.
jag visade tidigare hur man aktiverar ändringsspårning för en enda tabell med användargränssnittet. Jag föredrar att använda T-SQL för den här uppgiften, eftersom det är lättare att repetera. Aktivera ändringsspårning för tabellen jag skapade ovan kan göras som visas här:
kom ihåg att jag nämnde tidigare att ändringsspårning använder ett versions-ID för att spåra den aktuella versionen av de spårade tabellerna. Det versions-ID är vår tidslinjemarkör för att upptäcka ändringar. För att hämta det värdet finns det en mycket enkel funktion: CHANGE_TRACKING_CURRENT_VERSION(). Den används som visas nedan.
på mitt testsystem är detta värde 470 (eftersom jag har kört flera tester innan detta skrivs). Detta är utgångspunkten, och alla ändringar som görs från denna punkt framåt skulle utlösa ett nytt versionsnummer. Jag noterar det värdet och kommer nu att göra några ändringar i tabellen som beskrivs ovan. Jag lägger in en handfull rader för att visa hur förändringsspårning visar inlägg.
efter att ha satt in dessa sex rader kontrollerar jag värdet CHANGE_TRACKING_CURRENT_VERSION() igen och upptäcker att värdet nu är 476. Den har ökats med 6-en per rad införd, vilket är vad jag förväntar mig.
använda Förändringsspårningsfunktioner
därefter använder vi förändringsspårningsfunktionen CHANGETABLE() för att visa nettoändringarna i den här tabellen.
för att bryta ner detta:
- CHANGETABLE är den tabellvärderade systemfunktionen som returnerar listan över ändringar lagrade i ändringsspårning
- ändringar indikerar att jag letar efter ändringarna eftersom den angivna versionen
- @ver är variabeln jag ställer in för att lagra versionsnumret. CHANGETABLE returnerar alla resultat som återspeglar förändringar sedan den här versionen. Observera att du kan använda en variabel som jag gjorde, eller bara passera i ett skalärt tal (med hjälp av den bokstavliga 470 här skulle ha åstadkommit samma sak)
när jag kör koden ovan får jag följande resultatuppsättning.
detta berättar för mig versionen av insert och/eller update, operationen (i, U eller D för insert, update eller delete), kolumnmasken för uppdateringsoperationer (mer om detta tillfälligt) och primärnyckeln för raden som påverkas av denna ändring. Eftersom CHANGETABLE () returnerar en tabell, kunde jag enkelt gå med i det här resultatet tillbaka till originaltabellen för att se ändringsoperationen tillsammans med nuvarande data i den tabellen.
detta kommer att se lite annorlunda ut för en uppdateringsoperation. Därefter kör jag ett uppdateringsuttalande, men först kommer jag att notera den aktuella versionen av ändringsspårning (som fortfarande är 476).
nu uppdateringsuttalandet, som uppdaterar två rader i tabellen:
nu när jag kör CHANGETABLE () – koden ovanifrån, med den senaste förändringsspårningsversionen (476) som utgångspunkt, får jag en annan resultatuppsättning:
detta är metadata för alla ändringar sedan version 476, som bara innehåller de två raderna uppdaterade från UPPDATERINGSUTTALANDET ovan. Lägg märke till att skapningsversionen är null, eftersom den här ändringen var en uppdatering, inte en insats. Sys_change_columns-värdet är nu befolkat, men värdet visar inte riktigt vad som har ändrats (ännu). Det här är en bra tid att prata om förändringsspårningsfunktionen CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Den här funktionen kontrollerar om den angivna kolumnen har uppdaterats sedan den senaste versionen. Dess syntax är lite udda, men för att kontrollera om MiddleName uppdaterades skulle frågan se ut så här:
ärligt talat vet jag inte att jag någonsin har använt funktionen CHANGE_TRACKING_IS_COLUMN_IN_MASK. Det är lite ont eftersom du måste köra detta för varje kolumn du vill kontrollera. Det mesta av mitt arbete är i datalagring, och jag har stött på få fall där jag behöver veta exakt vilka kolumner som uppdaterades – jag vill bara veta om raden har uppdaterats. Men för andra scenarier (särskilt i OLTP) kan jag se behovet av detta.
jag har visat insatser och uppdateringar. Låt oss titta på hur en radering skulle se ut. Återigen noterar jag det aktuella versionsnumret – 478 – för nästa operation. Jag tar nu bort en rad data:
efter att ha raderat en rad kör jag CHANGETABLE() igen för att se vilka förändringsspårningsrapporter för den här åtgärden.
jag hittar den ena raden jag raderade i den senaste operationen, med SYS_CHANGE_OPERATION inställd på D (radera):
kom ihåg att versionsnumret gör skillnad här! Versionsnumret som skickas till CHANGETABLE () är utgångspunkten för eventuella ändringar som returneras av den funktionen. Genom denna övning har jag kontrollerat förändringsspårningsresultaten efter varje DML-operation. Jag kan dock ställa in startversionsnumret till något giltigt versionsnummer, eller helt enkelt använda NULL för att få alla tillgängliga förändringsspårningsresultat för den tabellen. För att demonstrera ställer jag in värdet tillbaka till version 470 – utgångspunkten före några uppdateringar – för att visa hur hela historiken skulle se ut. När jag kör CHANGETABLE() med vår ursprungliga ändringsspårningsversion får jag följande:
det finns ett par förutsägbara nyanser här. Först och främst visar raden som visar post-ID på 1 (vilket var Phoebe Buffay-posten som jag raderade) helt enkelt som en raderingsoperation, även om den här raden infogades och därefter raderades sedan startversionsnumret. Kom ihåg att det är delta som kommer att visas-varje operation mot den raden behålls inte i förändringsspårning. För IDs numrerade 2 och 4 – Vilka var de två raderna som jag infogade och därefter uppdaterade – visar SYS_CHANGE_OPERATION en insats, även om vi uppdaterade båda posterna efter insert. Tell är att SYS_CHANGE_VERSION och SYS_CHANGE_CREATION_VERSION på dessa rader inte matchar, vilket indikerar att den senaste ändringen inte var insatsen.
slutsats
Change tracking är ett enkelt och lätt sätt att upptäcka förändringar i SQL Server. Med hjälp av förändringsspårning kan du enkelt identifiera nya, ändrade och raderade data, vilket eliminerar behovet av brute-force jämförelser. I mitt nästa inlägg tittar jag på detta från ett ETL-perspektiv och integrerar förändringsspårning i en end-to-end-belastningsprocess.