change tracking voor SQL Server is een flexibele en eenvoudig te gebruiken technologie voor het bewaken van tabellen voor inserts, updates en verwijderingen. In deze post, Ik zal bespreken aan de slag met Change tracking in SQL Server, en zal een voorbeeld van hoe om te beginnen met het te laten zien.
Tracking wijzigen in SQL Server
tracking wijzigen is een lichtgewicht mechanisme om te volgen welke rijen zijn ingevoegd, bijgewerkt en verwijderd in tabellen die worden gecontroleerd door tracking wijzigen. Change tracking verscheen voor het eerst in SQL Server 2008 en is sindsdien in elke versie. Nog beter, change tracking is beschikbaar in elke editie van SQL Server, zelfs de free express edition.
In een notendop, zo werkt het: In tabellen waarin Change tracking is ingeschakeld, wordt de nettowijziging naar elke rij intern bijgehouden en is deze toegankelijk via Change tracking-functies. Elke wijziging heeft een versie-ID en er zal een nieuwe versie-ID zijn voor elke rij die wordt ingevoegd, bijgewerkt of verwijderd. De change tracking versie is een 8-byte integer (BIGINT) dat de meest recente change ID in die database weergeeft. Het is belangrijk op te merken dat de change tracking – versie niet tabelspecifiek is-een DML-bewerking in een bijgehouden tabel in elke database zal het versienummer verhogen. Het versienummer zal altijd sequentieel zijn, maar zal niet noodzakelijkerwijs aaneengesloten zijn binnen een enkele tabel als er meer dan één tabel is ingeschakeld voor het volgen van wijzigingen.
Change tracking is ingeschakeld op databaseniveau. Daarna, elke tabel die zal worden gecontroleerd moet individueel worden aangeworven in change tracking. Elke tabel die moet worden bewaakt door het volgen van wijzigingen moet een primaire sleutel hebben, omdat dit de rij-niveau-identificatie is die wordt gebruikt om DML-bewerkingen binnen het volgen van wijzigingen te rapporteren. Wanneer u het bijhouden van wijzigingen op tabelniveau inschakelt, kunt u ervoor kiezen om de kolom(s) te volgen die is gewijzigd in de meest recente update, waardoor u meer inzicht krijgt in wat is gewijzigd.
eenmaal ingesteld, is het volgen van wijzigingen een relatief eenvoudig proces. Er zijn een paar functies – met name CHANGE_TRACKING_CURRENT_VERSION () en CHANGETABLE (), die kunnen worden gebruikt om de huidige versie stempel in change tracking te controleren en de lijst van recente wijzigingen op te halen. Ik zal deze beide functies binnenkort demonstreren.
Change Tracking is Not Audit Logging
Ik zal voorzichtig zijn om de woorden audit of logging niet te gebruiken om change tracking te beschrijven. Laat ik duidelijk zijn: dit is geen volledig loggingsmechanisme. De wijzigingsgeschiedenis wordt helemaal niet bijgehouden-change tracking meldt alleen het feit dat er een wijziging is opgetreden, maar behoudt de versiegeschiedenis niet. Overweeg het geval van een Rij gegevens met een ID van 1234. Die rij wordt ingevoegd, vervolgens 5 keer bijgewerkt en vervolgens verwijderd. Tracking wijzigen zou de geschiedenis van invoegen, bijwerken en verwijderen niet tonen; integendeel, het zou alleen de netto verandering rapporteren, die rij ID 1234 werd verwijderd. Als uw laadproces gedetailleerde logboekgeschiedenis vereist voor elke wijziging (in plaats van alleen de delta van alle wijzigingen), moet u iets als Change Data capture gebruiken.
volgen van wijzigingen instellen in SQL Server
volgen van wijzigingen op tabelniveau inschakelen is een proces in twee stappen. Eerst moet het worden ingeschakeld in de database. Dit kan gedaan worden via de gebruikersinterface in de Database-Eigenschappen, op het tabblad Tracking wijzigen.
zoals getoond, is er niet veel te configureren bij het inschakelen van Change tracking in een database. Stel de Change Tracking waarde in op True om Change tracking voor die database in te stellen. Optioneel, de retentieperiode waarde kan ook worden getweaked. De standaardwaarde is 2 dagen, die ik in dit voorbeeld heb overschreden om in plaats daarvan 14 dagen te gebruiken. Zoals bij de meeste UI operaties, is er een T-SQL commando om hetzelfde te doen. Het commando om Change tracking in te stellen in deze database staat hieronder.
na deze stap is tracking wijzigen ingeschakeld,maar het volgt nog niets. Het moet nog worden ingeschakeld om elke tabel te volgen. De Table Properties UI maakt dit zeer eenvoudig.
zoals getoond, eenvoudig wijzigen van de Change Tracking waarde naar True maakt change tracking voor deze tabel. In dit voorbeeld heb ik er ook voor gekozen om de kolommen te volgen die zijn gewijzigd tijdens updates (meer hierover in een beetje).
de laatste stap hierboven zou worden herhaald voor elke tabel te volgen in change tracking. Zodra tracking wijzigen is ingeschakeld, worden alle wijzigingen (inserts, updates of verwijderingen) in die tabel opgeslagen in de tracking-cache wijzigen.
change Tracking instellen
in het bovenstaande voorbeeld ga ik enkele gegevens invoegen, bijwerken en verwijderen om aan te tonen hoe toegang te krijgen tot de change tracking data gegenereerd voor die DML operaties. Ter referentie, hier is de tabelstructuur.
ik heb eerder laten zien hoe je change tracking kunt inschakelen voor een enkele tabel met behulp van de gebruikersinterface. Ik gebruik liever T-SQL voor deze taak, omdat het gemakkelijker te herhalen is. Het inschakelen van change tracking voor de tabel die ik hierboven heb gemaakt kan worden gedaan zoals hier getoond:
bedenk dat ik eerder zei dat change tracking een versie-ID gebruikt om de huidige versie van de bijgehouden tabellen te volgen. Die versie-ID is onze tijdlijnmarkering voor het detecteren van wijzigingen. Om die waarde op te halen, is er een zeer eenvoudige functie: CHANGE_TRACKING_CURRENT_VERSION(). Het wordt gebruikt zoals hieronder aangegeven.
op mijn testsysteem is deze waarde 470 (omdat ik verschillende tests heb uitgevoerd voorafgaand aan dit schrijven). Dit is het startpunt,en eventuele wijzigingen vanaf dit punt naar voren zou leiden tot een nieuw versienummer. Ik zal een notitie maken van die waarde, en zal nu enkele wijzigingen aanbrengen in de tabel hierboven beschreven. Ik zal een handvol rijen invoegen om te laten zien hoe wijzig tracking inserts weergeeft.
na het invoegen van deze zes rijen, controleer ik de waarde CHANGE_TRACKING_CURRENT_VERSION() opnieuw en vind ik dat de waarde nu 476 is. Het is verhoogd met 6-Een per rij ingevoegd, dat is wat ik zou verwachten.
met behulp van Change Tracking functies
vervolgens zullen we de change tracking functie CHANGETABLE() gebruiken om de netto veranderingen op deze tabel te tonen.
om dit op te splitsen:
- CHANGETABLE is de tabel-waarde systeem functie die de lijst van wijzigingen opgeslagen in change tracking
- wijzigingen geeft aan dat ik op zoek ben naar de wijzigingen sinds de opgegeven versie
- @ver is de variabele die ik heb ingesteld om het versienummer op te slaan. CHANGETABLE retourneert alle resultaten die de wijzigingen sinds deze versie weerspiegelen. Merk op dat je een variabele kunt gebruiken zoals ik deed, of gewoon een scalair getal doorgeven (met de letterlijke 470 hier zou hetzelfde hebben bereikt)
wanneer ik de bovenstaande code uit voer, ontvang ik de volgende result set.
dit vertelt me de versie van het invoegen en/of bijwerken, de bewerking (I, U, of D voor respectievelijk invoegen, bijwerken of verwijderen), het kolommasker voor updatebewerkingen (meer hierover op dit moment), en de primaire sleutel van de rij beïnvloed door deze wijziging. Omdat CHANGETABLE () een tabel retourneert, kon ik gemakkelijk dit resultaat terugzetten naar de oorspronkelijke tabel om de verandering operatie samen met de huidige gegevens in die tabel te zien.
dit zal er iets anders uitzien voor een update operatie. Vervolgens zal ik een update statement uit te voeren, maar eerst, Ik Ga naar de huidige versie van change tracking noteren (die nog steeds 476).
nu het update statement, dat twee rijen in de tabel bijwerkt:
als ik nu de CHANGETABLE() code van boven uit voer, gebruikmakend van de recentere change tracking versie (476) als startpunt, krijg ik een ander resultaat set:
dit is de metagegevens voor alle wijzigingen sinds versie 476, die alleen de twee rijen bevat die zijn bijgewerkt uit het UPDATE statement hierboven. Merk op dat de creatieversie null is, omdat deze wijziging een update was, geen insert. Ook is de waarde SYS_CHANGE_COLUMNS nu ingevuld, hoewel de waarde ons niet echt laat zien wat er (nog) is veranderd. Dit is een goed moment om te praten over de change tracking functie CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Deze functie controleert of de opgegeven kolom is bijgewerkt sinds de meest recente versie. De syntaxis is een beetje eigenzinnig, maar om te controleren of de Middlenaam is bijgewerkt, zou de query er als volgt uitzien:
eerlijk gezegd Weet ik niet dat ik ooit de functie CHANGE_TRACKING_IS_COLUMN_IN_MASK heb gebruikt. Het is een beetje lastig omdat je dit moet uitvoeren voor elke kolom die je wilt controleren. Het grootste deel van mijn werk is in data warehousing, en ik heb in een paar gevallen waarin ik moet precies weten welke kolommen zijn bijgewerkt – Ik wil gewoon weten of de rij is bijgewerkt. Echter, voor andere scenario ‘ s (vooral in OLTP), ik zie de noodzaak voor dit.
ik heb inserts en updates gedemonstreerd. Laten we eens kijken hoe een delete eruit zou zien. Nogmaals, ik zal een notitie maken van het huidige versienummer-478-voor de volgende operatie. Ik zal nu een Rij gegevens verwijderen:
na het verwijderen van een rij, zal ik CHANGETABLE() opnieuw uitvoeren om te zien welke change tracking rapporten voor deze operatie.
ik vind de ene rij die ik heb verwijderd in de laatste bewerking, met de SYS_CHANGE_OPERATION ingesteld op D (verwijderen):
nu, vergeet niet dat het versienummer maakt een verschil hier! Het versienummer dat is doorgegeven aan CHANGETABLE () is het startpunt voor alle wijzigingen die door die functie worden geretourneerd. Door middel van deze oefening heb ik de change tracking resultaten na elke DML operatie gecontroleerd. Echter, Ik kan het begin versienummer instellen op een geldig versienummer, of gewoon NULL gebruiken om alle beschikbare Change tracking resultaten voor die tabel te krijgen. Om aan te tonen, Ik zal de waarde terug te zetten naar versie 470 – het uitgangspunt voor eventuele updates – om te laten zien hoe de volledige geschiedenis eruit zou zien. Wanneer ik CHANGETABLE () opnieuw uitvoeren met behulp van onze originele change tracking versie, krijg ik de volgende:
er zijn een paar voorspelbare nuances hier. Allereerst, de rij met record ID van 1 (Dat was de Phoebe Buffay record ik verwijderde) verschijnt gewoon als een delete operatie, hoewel deze rij werd ingevoegd en vervolgens verwijderd sinds de start versienummer. Vergeet niet, het is de delta die zal worden getoond – elke operatie tegen die rij wordt niet behouden in change tracking. Voor IDs genummerd 2 en 4 – Dat waren de twee rijen die ik ingevoegd en vervolgens bijgewerkt – de SYS_CHANGE_OPERATION toont een insert, hoewel we beide records bijgewerkt na invoegen. De tell is dat de SYS_CHANGE_VERSION en SYS_CHANGE_CREATION_VERSION op deze rijen niet overeenkomen, wat aangeeft dat de meest recente wijziging niet de insert was.
conclusie
Change tracking is een eenvoudig en lichtgewicht middel om veranderingen te detecteren in SQL Server. Met behulp van change tracking maakt eenvoudige identificatie van nieuwe, gewijzigde en verwijderde gegevens, waardoor de noodzaak voor brute-force vergelijkingen. In mijn volgende post, Ik zal kijken naar dit vanuit een ETL perspectief, integratie van verandering volgen in een end-to-end load proces.