Die Änderungsverfolgung für SQL Server ist eine flexible und benutzerfreundliche Technologie zum Überwachen von Tabellen auf Einfügungen, Aktualisierungen und Löschungen. In diesem Beitrag werde ich die ersten Schritte mit der Änderungsverfolgung in SQL Server besprechen und ein Beispiel für die ersten Schritte zeigen.
Änderungsverfolgung in SQL Server
Die Änderungsverfolgung ist ein leichter Mechanismus zum Verfolgen, welche Zeilen in Tabellen eingefügt, aktualisiert und gelöscht wurden, die von der Änderungsverfolgung überwacht werden. Änderungsverfolgung erschien zuerst in SQL Server 2008 und ist seitdem in jeder Version enthalten. Noch besser ist, dass die Änderungsverfolgung in jeder Edition von SQL Server verfügbar ist, sogar in der kostenlosen Express Edition.
Kurz gesagt, so funktioniert es: In Tabellen mit aktivierter Änderungsverfolgung wird die Nettoänderung jeder Zeile intern verfolgt und ist über Änderungsverfolgungsfunktionen zugänglich. An jede Änderung ist eine Versions-ID angehängt, und für jede Zeile, die eingefügt, aktualisiert oder gelöscht wird, wird eine neue Versions-ID angezeigt. Die Änderungsverfolgungsversion ist eine 8-Byte-Ganzzahl (BIGINT), die die letzte Änderungs-ID in dieser Datenbank widerspiegelt. Es ist wichtig zu beachten, dass die Änderungsverfolgungsversion nicht tabellenspezifisch ist – eine DML-Operation in einer verfolgten Tabelle in jeder Datenbank erhöht die Versionsnummer. Die Versionsnummer ist immer sequenziell, aber nicht unbedingt zusammenhängend innerhalb einer einzelnen Tabelle, wenn mehr als eine Tabelle für die Änderungsverfolgung aktiviert ist.
Änderungsverfolgung ist auf Datenbankebene aktiviert. Danach muss jede Tabelle, die überwacht wird, einzeln in die Änderungsverfolgung aufgenommen werden. Jede Tabelle, die von der Änderungsverfolgung überwacht werden soll, muss über einen Primärschlüssel verfügen, da dies der Bezeichner auf Zeilenebene ist, der zum Melden von DML-Vorgängen innerhalb der Änderungsverfolgung verwendet wird. Wenn Sie die Änderungsverfolgung auf Tabellenebene aktivieren, können Sie die Spalte (n) verfolgen, die in der letzten Aktualisierung geändert wurden.
Einmal eingerichtet, ist die Verwendung von Change Tracking ein relativ einfacher Prozess. Es gibt einige Funktionen – vor allem CHANGE_TRACKING_CURRENT_VERSION() und CHANGETABLE() , mit denen der aktuelle Versionsstempel in der Änderungsverfolgung überprüft und die Liste der letzten Änderungen abgerufen werden kann. Ich werde diese beiden Funktionen in Kürze demonstrieren.
Änderungsverfolgung ist keine Überwachungsprotokollierung
Ich werde darauf achten, die Wörter Audit oder Protokollierung nicht zu verwenden, um die Änderungsverfolgung zu beschreiben. Lassen Sie mich klarstellen: Dies ist kein vollständiger Protokollierungsmechanismus. Die Änderungshistorie wird überhaupt nicht verfolgt – die Änderungsverfolgung meldet nur die Tatsache, dass eine Änderung stattgefunden hat, behält jedoch nicht die Versionsgeschichte bei. Betrachten Sie den Fall einer Datenzeile mit der ID 1234. Diese Zeile wird eingefügt, dann 5 Mal aktualisiert und dann gelöscht. Die Änderungsverfolgung zeigt den Verlauf von Einfügen, Aktualisieren und Löschen nicht an; vielmehr würde es nur die Nettoänderung melden, dass die Zeilen-ID 1234 gelöscht wurde. Wenn Ihr Ladevorgang für jede Änderung einen detaillierten Protokollierungsverlauf erfordert (und nicht nur das Delta aller Änderungen), müssen Sie so etwas wie die Änderungsdatenerfassung verwenden.
Einrichten der Änderungsverfolgung in SQL Server
Das Aktivieren der Änderungsverfolgung auf Tabellenebene ist ein zweistufiger Prozess. Zunächst muss es in der Datenbank aktiviert sein. Dies kann über die Benutzeroberfläche in den Datenbankeigenschaften auf der Registerkarte Änderungsverfolgung erfolgen.
Wie gezeigt, muss beim Aktivieren der Änderungsverfolgung in einer Datenbank nicht viel konfiguriert werden. Setzen Sie einfach den Änderungsverfolgungswert auf True, um die Änderungsverfolgung für diese Datenbank einzurichten. Optional kann auch der Wert für die Aufbewahrungsfrist angepasst werden. Der Standardwert ist 2 Tage, den ich in diesem Beispiel überschrieben habe, um stattdessen 14 Tage zu verwenden. Wie bei den meisten UI-Operationen gibt es einen T-SQL-Befehl, um dasselbe zu tun. Der Befehl zum Einrichten der Änderungsverfolgung in dieser Datenbank ist unten aufgeführt.
Nach diesem Schritt ist die Änderungsverfolgung aktiviert, verfolgt jedoch noch nichts. Es muss noch aktiviert werden, damit jede Tabelle verfolgt werden kann. Die Benutzeroberfläche für Tabelleneigenschaften macht dies sehr einfach.
Wie gezeigt, wird durch einfaches Ändern des Änderungsverfolgungswerts in True die Änderungsverfolgung für diese Tabelle aktiviert. In diesem Beispiel habe ich mich auch dafür entschieden, die Spalten zu verfolgen, die sich während der Updates geändert haben (mehr dazu gleich).
Der letzte Schritt oben würde für jede Tabelle wiederholt, die in der Änderungsverfolgung verfolgt werden soll. Sobald die Änderungsverfolgung aktiviert ist, werden alle Änderungen (Einfügungen, Aktualisierungen oder Löschungen) an dieser Tabelle im Cache für die Änderungsverfolgung gespeichert.
Einrichten der Änderungsverfolgung
Im obigen Beispiel werde ich einige Daten einfügen, aktualisieren und löschen, um zu demonstrieren, wie auf die für diese DML-Vorgänge generierten Änderungsverfolgungsdaten zugegriffen werden kann. Als Referenz ist hier die Tabellenstruktur.
Ich habe bereits gezeigt, wie Sie die Änderungsverfolgung für eine einzelne Tabelle über die Benutzeroberfläche aktivieren können. Ich bevorzuge die Verwendung von T-SQL für diese Aufgabe, da es leichter wiederholbar ist. Das Aktivieren der Änderungsverfolgung für die oben erstellte Tabelle kann wie folgt erfolgen:
Denken Sie daran, dass ich bereits erwähnt habe, dass die Änderungsverfolgung eine Versions-ID verwendet, um die aktuelle Version der verfolgten Tabellen zu verfolgen. Diese Versions-ID ist unser Timeline-Marker zum Erkennen von Änderungen. Um diesen Wert abzurufen, gibt es eine sehr einfache Funktion: CHANGE_TRACKING_CURRENT_VERSION() . Es wird wie unten gezeigt verwendet.
Auf meinem Testsystem ist dieser Wert 470 (da ich vor diesem Schreiben mehrere Tests ausgeführt habe). Dies ist der Ausgangspunkt, und alle ab diesem Zeitpunkt vorgenommenen Änderungen würden eine neue Versionsnummer auslösen. Ich werde diesen Wert notieren und nun einige Änderungen an der oben beschriebenen Tabelle vornehmen. Ich werde eine Handvoll Zeilen einfügen, um zu zeigen, wie die Änderungsverfolgung Einfügungen anzeigt.
Nachdem ich diese sechs Zeilen eingefügt habe, überprüfe ich erneut den Wert CHANGE_TRACKING_CURRENT_VERSION() und stelle fest, dass der Wert jetzt 476 ist. Es wurde um 6 erhöht – eins pro eingefügter Zeile, was ich erwarten würde.
Verwenden von Änderungsverfolgungsfunktionen
Als nächstes verwenden wir die Änderungsverfolgungsfunktion CHANGETABLE(), um die Nettoänderungen in dieser Tabelle anzuzeigen.
Um dies aufzuschlüsseln:
- CHANGETABLE ist die tabellenwertige Systemfunktion, die die Liste der Änderungen zurückgibt, die in change tracking
- CHANGES zeigt an, dass ich seit der angegebenen Version nach den Änderungen suche
- @ver ist die Variable, die ich zum Speichern der Versionsnummer eingerichtet habe. CHANGETABLE gibt alle Ergebnisse zurück, die Änderungen seit dieser Version widerspiegeln. Beachten Sie, dass Sie eine Variable wie ich verwenden oder einfach eine skalare Zahl übergeben können (die Verwendung des Literals 470 hätte hier dasselbe erreicht)
Wenn ich den obigen Code ausführe, erhalte ich die folgende Ergebnismenge.
Dies gibt die Version des Einfügens und / oder Aktualisierens an, die Operation (I, U oder D für Einfügen, Aktualisieren bzw. Löschen), die Spaltenmaske für Aktualisierungsvorgänge (dazu momentan mehr) und der Primärschlüssel der Zeile, die von dieser Änderung betroffen ist. Da CHANGETABLE() eine Tabelle zurückgibt, könnte ich diese Ergebnismenge problemlos mit der ursprünglichen Tabelle verknüpfen, um den Änderungsvorgang zusammen mit den aktuellen Daten in dieser Tabelle anzuzeigen.
Dies wird bei einem Update-Vorgang etwas anders aussehen. Als nächstes werde ich eine Update-Anweisung ausführen, aber zuerst werde ich die aktuelle Version der Änderungsverfolgung notieren (die immer noch 476 ist).
Nun die Update-Anweisung, die zwei Zeilen in der Tabelle aktualisiert:
Wenn ich jetzt den CHANGETABLE() -Code von oben ausführe und die neuere Änderungsnachverfolgungsversion (476) als Ausgangspunkt verwende, erhalte ich eine andere Ergebnismenge:
Dies sind die Metadaten für alle Änderungen seit Version 476, die nur die beiden Zeilen enthalten, die aus der obigen UPDATE-Anweisung aktualisiert wurden. Beachten Sie, dass die Erstellungsversion null ist, da diese Änderung ein Update und kein Einfügen war. Außerdem wird jetzt der Wert SYS_CHANGE_COLUMNS gefüllt, obwohl der Wert nicht wirklich anzeigt, was sich (noch) geändert hat. Dies ist ein guter Zeitpunkt, um über die Änderungsverfolgungsfunktion CHANGE_TRACKING_IS_COLUMN_IN_MASK () zu sprechen. Diese Funktion prüft, ob die angegebene Spalte seit der letzten Version aktualisiert wurde. Die Syntax ist etwas schrullig, aber um zu überprüfen, ob der MiddleName aktualisiert wurde, würde die Abfrage folgendermaßen aussehen:
Ehrlich gesagt weiß ich nicht, dass ich jemals die Funktion CHANGE_TRACKING_IS_COLUMN_IN_MASK verwendet habe. Es ist ein bisschen schmerzhaft, weil Sie dies für jede Spalte ausführen müssen, die Sie überprüfen möchten. Der größte Teil meiner Arbeit liegt im Data Warehousing, und ich bin auf wenige Fälle gestoßen, in denen ich genau wissen muss, welche Spalten aktualisiert wurden – ich möchte nur wissen, ob die Zeile aktualisiert wurde. Für andere Szenarien (insbesondere in OLTP) kann ich jedoch die Notwendigkeit dafür erkennen.
Ich habe Einfügungen und Aktualisierungen demonstriert. Schauen wir uns an, wie ein Delete aussehen würde. Auch hier werde ich die aktuelle Versionsnummer – 478 – für die nächste Operation notieren. Ich lösche jetzt eine Datenzeile:
Nachdem ich eine Zeile gelöscht habe, führe ich CHANGETABLE() erneut aus, um zu sehen, welche Änderungsnachverfolgungsberichte für diesen Vorgang vorliegen.
Ich finde die eine Zeile, die ich in der letzten Operation gelöscht habe, wobei SYS_CHANGE_OPERATION auf D (delete):
Denken Sie daran, dass die Versionsnummer hier einen Unterschied macht! Die an CHANGETABLE() übergebene Versionsnummer ist der Ausgangspunkt für alle von dieser Funktion zurückgegebenen Änderungen. Durch diese Übung habe ich die Ergebnisse der Änderungsverfolgung nach jeder DML-Operation überprüft. Ich kann jedoch die Startversionsnummer auf eine beliebige gültige Versionsnummer setzen oder einfach NULL verwenden, um alle verfügbaren Änderungsverfolgungsergebnisse für diese Tabelle abzurufen. Um dies zu demonstrieren, setze ich den Wert wieder auf Version 470 – den Ausgangspunkt vor allen Updates –, um zu zeigen, wie der vollständige Verlauf aussehen würde. Wenn ich CHANGETABLE () mit unserer ursprünglichen Änderungsverfolgungsversion erneut ausführe, erhalte ich Folgendes:
Hier gibt es einige vorhersehbare Nuancen. Zunächst wird die Zeile mit der Datensatz-ID 1 (der von mir gelöschte Phoebe Buffay-Datensatz) einfach als Löschvorgang angezeigt, obwohl diese Zeile seit der Startversionsnummer eingefügt und anschließend gelöscht wurde. Denken Sie daran, dass das Delta angezeigt wird – jede Operation für diese Zeile wird in der Änderungsverfolgung nicht beibehalten. Für die IDs 2 und 4 – die beiden Zeilen, die ich eingefügt und anschließend aktualisiert habe – zeigt SYS_CHANGE_OPERATION eine Einfügung an, obwohl wir beide Datensätze nach der Einfügung aktualisiert haben. Das Problem ist, dass SYS_CHANGE_VERSION und SYS_CHANGE_CREATION_VERSION in diesen Zeilen nicht übereinstimmen, was darauf hinweist, dass die letzte Änderung nicht die Einfügung war.
Fazit
Änderungsnachverfolgung ist ein einfaches und leichtes Mittel zur Änderungserkennung in SQL Server. Die Verwendung von Änderungsverfolgung ermöglicht die einfache Identifizierung neuer, geänderter und gelöschter Daten, wodurch Brute-Force-Vergleiche entfallen. In meinem nächsten Beitrag werde ich dies aus einer ETL-Perspektive betrachten und die Änderungsverfolgung in einen End-to-End-Ladeprozess integrieren.