urmărirea modificărilor pentru SQL Server este o tehnologie flexibilă și ușor de utilizat pentru monitorizarea tabelelor pentru inserții, actualizări și ștergeri. În acest post, voi discuta despre Noțiuni de bază cu schimbarea de urmărire în SQL Server, și va arăta un exemplu de cum să începeți cu ea.
urmărirea modificărilor în SQL Server
urmărirea modificărilor este un mecanism ușor pentru urmărirea rândurilor care au fost inserate, actualizate și șterse în tabelele monitorizate de urmărirea modificărilor. Schimbarea de urmărire a apărut pentru prima dată în SQL Server 2008 și a fost în fiecare versiune de atunci. Și mai bine, urmărirea modificărilor este disponibilă în fiecare ediție a SQL Server, chiar și în ediția gratuită express.
pe scurt, iată cum funcționează: În tabelele care au activată urmărirea modificărilor, modificarea netă a fiecărui rând este urmărită intern și este accesibilă prin funcțiile de urmărire a modificărilor. Fiecare modificare are atașat un ID de versiune și va exista un nou ID de versiune pentru fiecare rând introdus, actualizat sau șters. Versiunea de urmărire a modificărilor este un număr întreg de 8 octeți (BIGINT) care reflectă cel mai recent ID de Modificare din Baza de date respectivă. Este important să rețineți că versiunea de urmărire a modificărilor nu este specifică tabelului – o operație DML în orice tabel urmărit din fiecare bază de date va crește numărul versiunii. Numărul versiunii va fi întotdeauna secvențial, dar nu va fi neapărat contigu într-un singur tabel dacă există mai multe tabele activate pentru urmărirea modificărilor.
urmărirea modificărilor este activată la nivelul bazei de date. După aceea, fiecare tabel care va fi monitorizat trebuie înscris individual în urmărirea modificărilor. Fiecare tabel care trebuie monitorizat prin urmărirea modificărilor trebuie să aibă o cheie primară, deoarece acesta este identificatorul la nivel de rând utilizat pentru a raporta operațiunile LMD din urmărirea modificărilor. Când activați urmărirea modificărilor la nivel de tabel, puteți opta pentru urmărirea coloanelor modificate în cea mai recentă actualizare, ceea ce vă va oferi o vizibilitate mai mare asupra a ceea ce a fost modificat.
Odată configurat, utilizarea urmăririi modificărilor este un proces relativ simplu. Există câteva funcții – mai ales, CHANGE_TRACKING_CURRENT_VERSION () și CHANGETABLE (), care pot fi utilizate pentru a verifica ștampila versiunii curente în urmărirea modificărilor și pentru a prelua lista modificărilor recente. Voi demonstra ambele funcții în scurt timp.
schimbarea de urmărire nu este de audit logare
am de gând să fie atenți să nu folosească cuvintele audit sau logare pentru a descrie schimbarea de urmărire. Permiteți-mi să fiu clar: acesta nu este un mecanism complet de înregistrare. Istoricul modificărilor nu este urmărit deloc-urmărirea modificărilor raportează doar faptul că a avut loc o modificare, dar nu păstrează istoricul versiunilor. Luați în considerare cazul unui rând de date cu un ID de 1234. Acest rând este introdus, apoi actualizat de 5 ori și apoi șters. Urmărirea modificărilor nu ar afișa istoricul insertului, actualizării și ștergerii; mai degrabă, ar raporta doar modificarea netă, ID-ul rândului 1234 a fost șters. Dacă procesul de încărcare necesită istoric detaliat de logare pentru fiecare modificare (mai degrabă decât doar delta tuturor modificărilor), va trebui să utilizați ceva de genul change data capture.
Configurarea urmăririi modificărilor în SQL Server
activarea urmăririi modificărilor la nivel de tabel este un proces în două etape. În primul rând, trebuie să fie activat în baza de date. Acest lucru se poate face prin UI în proprietățile bazei de date, în fila modificare urmărire.
după cum se arată, nu există prea multe de configurat atunci când activați urmărirea modificărilor într-o bază de date. Pur și simplu setați valoarea de urmărire a modificărilor la True pentru a configura urmărirea modificărilor pentru baza de date respectivă. Opțional, valoarea perioadei de retenție poate fi optimizat, de asemenea. Valoarea implicită este de 2 zile, pe care am înlocuit-o în acest exemplu pentru a utiliza în schimb 14 zile. Ca și în cazul majorității operațiunilor UI, există o comandă T-SQL pentru a face același lucru. Comanda pentru configurarea urmăririi modificărilor în această bază de date este mai jos.
după acest pas, urmărirea modificărilor este activată, dar nu urmărește încă nimic. Trebuie să fie activat pentru ca fiecare tabel să fie urmărit. UI proprietăți De Masă face acest lucru foarte ușor.
după cum se arată, simpla modificare a valorii de urmărire a modificărilor în True permite urmărirea modificărilor pentru acest tabel. În acest exemplu am optat, de asemenea, pentru a urmări coloanele modificate în timpul actualizărilor (mai multe despre acest lucru într-un pic).
ultimul pas de mai sus va fi repetat pentru ca fiecare tabel să fie urmărit în urmărirea modificărilor. După activarea urmăririi modificărilor, orice modificări (inserturi, actualizări sau ștergeri) ale tabelului respectiv vor fi stocate în memoria cache a urmăririi modificărilor.
Configurarea urmăririi modificărilor
pentru exemplul de mai sus, voi insera, actualiza și șterge unele date pentru a demonstra cum să accesați datele de urmărire a modificărilor generate pentru acele operații DML. Pentru referință, aici este structura tabelului.
am arătat mai devreme cum să activați urmărirea modificărilor pentru un singur tabel utilizând interfața de utilizare. Prefer să folosesc T-SQL pentru această sarcină, deoarece este mai ușor de repetat. Activarea urmăririi modificărilor pentru tabelul pe care l-am creat mai sus se poate face așa cum se arată aici:
reamintim că am menționat mai devreme că urmărirea modificărilor utilizează un ID de versiune pentru a urmări versiunea curentă a tabelelor urmărite. ID-ul versiunii este marcatorul nostru de cronologie pentru detectarea modificărilor. Pentru a prelua această valoare, există o funcție foarte simplă: CHANGE_TRACKING_CURRENT_VERSION (). Se utilizează așa cum se arată mai jos.
în sistemul meu de testare, această valoare este 470 (deoarece am rulat mai multe teste înainte de această scriere). Acesta este punctul de plecare, și orice modificări făcute de la acest punct înainte ar declanșa un nou număr de versiune. Voi face o notă de această valoare, și va face acum unele modificări la tabelul descris mai sus. Voi insera o mână de rânduri pentru a arăta modul în care se afișează inserțiile de urmărire a modificărilor.
după inserarea acestor șase rânduri, verific din nou valoarea CHANGE_TRACKING_CURRENT_VERSION() și constat că valoarea este acum 476. Acesta a fost crescut cu 6 – unul pe rând inserat, ceea ce m-aș aștepta.
utilizarea funcțiilor de urmărire a modificărilor
în continuare, vom folosi funcția de urmărire a modificărilor CHANGETABLE() pentru a afișa modificările nete din acest tabel.
pentru a rupe acest jos:
- CHANGETABLE este funcția de sistem cu valoare de tabel care va returna lista modificărilor stocate în urmărirea modificărilor
- modificări indică faptul că caut modificările deoarece versiunea specificată
- @ver este variabila pe care am configurat-o pentru a stoca numărul versiunii. CHANGETABLE va returna toate rezultatele care reflectă modificările de la această versiune. Rețineți că puteți utiliza o variabilă așa cum am făcut-o sau pur și simplu treceți într-un număr scalar (folosind literalul 470 aici ar fi realizat același lucru)
când rulez codul de mai sus, primesc următorul set de rezultate.
aceasta îmi spune versiunea de inserare și/sau actualizare, operația (I, U sau D pentru inserare, actualizare sau ștergere, respectiv), masca de coloană pentru operațiunile de actualizare (Mai multe despre acest moment) și cheia primară a rândului afectat de această modificare. Deoarece CHANGETABLE () returnează un tabel, aș putea alătura cu ușurință acest set de rezultate înapoi la tabelul original pentru a vedea operația de schimbare împreună cu datele curente din acel tabel.
acest lucru va arăta puțin diferit pentru o operație de actualizare. În continuare voi executa o declarație de actualizare, dar mai întâi, voi nota versiunea curentă a urmăririi modificărilor (care este încă 476).
acum instrucțiunea de actualizare, care va actualiza două rânduri în tabel:
acum, când rulez codul CHANGETABLE () de mai sus, folosind versiunea mai recentă de urmărire a modificărilor (476) ca punct de plecare, obțin un set de rezultate diferit:
aceasta este metadata pentru toate modificările de la versiunea 476, care include doar cele două rânduri actualizate din instrucțiunea de actualizare de mai sus. Observați că versiunea de creare este nulă, deoarece această modificare a fost o actualizare, nu o inserare. De asemenea, valoarea SYS_CHANGE_COLUMNS este acum populată, deși valoarea nu ne arată cu adevărat ce s-a schimbat (încă). Acesta este un moment bun pentru a vorbi despre funcția de urmărire a modificărilor CHANGE_TRACKING_IS_COLUMN_IN_MASK(). Această funcție va verifica dacă coloana specificată a fost actualizată de la cea mai recentă versiune. Sintaxa sa este puțin ciudată, dar pentru a verifica dacă MiddleName a fost actualizat, interogarea ar arăta astfel:
sincer, nu știu că am folosit vreodată funcția CHANGE_TRACKING_IS_COLUMN_IN_MASK. Este un pic de durere pentru că trebuie să rulați acest lucru pentru fiecare coloană pe care doriți să o verificați. Cea mai mare parte a muncii mele este în depozitarea datelor și am întâlnit puține cazuri în care trebuie să știu exact ce coloane au fost actualizate – vreau doar să știu dacă rândul a fost actualizat. Cu toate acestea, pentru alte scenarii (în special în OLTP), pot vedea necesitatea acestui lucru.
am demonstrat inserții și actualizări. Să ne uităm la cum ar arăta o ștergere. Din nou, voi nota numărul versiunii curente – 478 – pentru următoarea operație. Voi șterge acum un rând de date:
după ce am șters un rând, voi rula CHANGETABLE() din nou pentru a vedea ce rapoarte de urmărire a modificărilor pentru această operație.
găsesc un rând pe care l-am șters în ultima operație, cu sys_change_operation setat la D (șterge):
acum, amintiți-vă că numărul versiunii face diferența aici! Numărul de versiune trecut în CHANGETABLE () este punctul de plecare pentru orice modificări returnate de această funcție. Prin acest exercițiu am verificat rezultatele urmăririi modificărilor după fiecare operație DML. Cu toate acestea, pot seta numărul versiunii de pornire la orice număr de versiune valid sau pur și simplu pot folosi NULL pentru a obține toate rezultatele disponibile de urmărire a modificărilor pentru acel tabel. Pentru a demonstra, voi seta valoarea înapoi la versiunea 470 – punctul de plecare înainte de orice actualizări-pentru a arăta cum ar arăta istoricul complet. Când rulez din nou CHANGETABLE () folosind versiunea noastră originală de urmărire a modificărilor, primesc următoarele:
există câteva nuanțe previzibile aici. În primul rând, rândul care arată ID-ul înregistrării 1 (care a fost înregistrarea Phoebe Buffay pe care am șters-o) apare pur și simplu ca o operație de ștergere, chiar dacă acest rând a fost introdus și ulterior șters de la numărul versiunii de pornire. Amintiți – vă, delta va fi afișată-fiecare operație împotriva acelui rând nu este reținută în urmărirea modificărilor. Pentru ID – urile numerotate 2 și 4 – care au fost cele două rânduri pe care le-am inserat și ulterior actualizate-SYS_CHANGE_OPERATION arată un insert, chiar dacă am actualizat ambele înregistrări după insert. Tell este că SYS_CHANGE_VERSION și sys_change_creation_version pe aceste rânduri nu se potrivesc, indicând faptul că cea mai recentă modificare nu a fost inserarea.
concluzie
urmărirea modificărilor este un mijloc simplu și ușor de detectare a modificărilor în SQL Server. Utilizarea change tracking permite identificarea ușoară a datelor noi, modificate și șterse, eliminând necesitatea comparațiilor brute-force. În următoarea mea postare, voi analiza acest lucru dintr-o perspectivă ETL, integrând urmărirea schimbărilor într-un proces de încărcare end-to-end.