Redgate Hub
Introduksjon
det er flere grunner til at du kanskje må sammenligne tabeller eller resultater.
- Noen ganger trenger man bare å vite om tabellene inneholder data som er like eller forskjellige; Ingen detaljer: bare ja eller nei. Dette er typisk med test påstander, der du bare trenger å vite om din rutine eller batch produserer et resultat med de riktige dataene i den. når det leveres med spesielle vales for parametrene. Det er enten feil eller riktig
- Av og til må du vite hvilke rader som har endret seg uten, kanskje, å være bestemt om hvilke kolonner endret og hvordan.
- Det er tider når du har et stort bord i form av både kolonner og rader, og du trenger noe som viser deg spesifikt kolonnen(e) som endret verdien. Du vil kanskje også dette når du sporer ned en feil i en rutine som ellers kan kreve at du kaster bort tid på å skanne ‘for øye’.
Vi skal takle disse tre ganske forskjellige oppgaver I SQL
Hvis to tabeller har et annet antall rader, kan de selvfølgelig ikke være det samme. Det er imidlertid tider når du trenger å vite om Table_B inneholder alle rader Av Table_A, uten forskjeller. Hvis du ønsker flere detaljer, kan du til og med ønske å vite radene i en tabell som ikke er felles, eller de vanlige radene, som angitt av primærnøkkelen, som var forskjellige. Hvorfor holde fast ved å sammenligne bare to tabeller? Det finnes måter å sammenligne så mange du trenger. (som for eksempel når du sammenligner metadataene i flere øyeblikksbilder i databasen). Ja, det er mange variasjoner
Du har verktøy og funksjoner for å gjøre dette, sikkert?
det er alltid et sted for verktøy som SQL Data Compare, Tabeldiff, tSQLt eller Change Data Capture. Mye avhenger av omstendigheter og type oppgave. Problemet med å gjøre revisjoner på endringer i data i et levende system er et eget emne, som er synkronisering av tabeller og databaser. Sammenligning AV XML-dokumenter er også ute av omfang. Jeg er mest sannsynlig å bruke TSQL teknikker for å sammenligne tabeller når:
Utvikle…
i løpet av å utvikle en database blir mange tabeller sammenlignet. Det er ikke bare de store greiene: Hver tabellverdig funksjon, for Eksempel, trenger en testsele i byggeskriptet som sørger for at den gjør det du tror det skal gjøre under alle tenkelige testforhold, og inkorporerer alle de ekle kanttilfellene der det har blitt fanget av testerne tidligere. Hver lagret prosedyre trenger en test for å sikre at prosessen som den utfører, gjør akkurat det som er ment og ingenting annet.Det var en tid at byggeaktiviteten var ganske rolig, men når du har en nattlig bygge-og integrasjonstest, er det best å automatisere det helt og bli kvitt ork.
ETL
når du automatiserer lasting av data i et system, må du ofte teste ulike forhold. Trenger du å oppdatere eksisterende versjoner av radene, samt sette inn de nye? Trenger du en felle for å forhindre dupliserte oppføringer, eller til og med slette eksisterende oppføringer?
Sette opp testdataene.
skriptene i denne artikkelen bruker alle en tabell fra den ærverdige PUBS-databasen. Vi skal bruke forfattertabellen, men vil øke antall rader litt til 5000 for å få en størrelse som er litt mer realistisk. Jeg har gitt kilden til tabellen med artikkelen.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy LEGG TIL BEGRENSNING PK_AUTHORSCOPY PRIMÆRNØKKEL GRUPPERT
(au_id) PÅ PRIMÆR
GÅ
|
og deretter endret noen av radene.
|
1
2
3
4
|
så nå skal de to tabellene være overveiende det samme med noen få mindre endringer i adressefeltet
testing for å se om bordene er forskjellige.
Noen ganger vil du bare vite om tabellene er de samme. Et eksempel på dette ville være å sjekke AT EN TVF fungerer som den skal ved å sammenligne resultatet til et eksisterende bord med de riktige resultatene. Den vanlige måten å gjøre dette på er medCHECKSUM() – gruppen av funksjoner I SQL Server, fordi de er veldig raske.
Ved Hjelp Av Sjekksummer
du kan bruke BINARY_CHECKSUM – funksjonen for å sjekke om tabellene er de samme: vel, omtrent det samme. Det er raskt, men det er ikke perfekt, som jeg vil demonstrere om et øyeblikk. Hvis du har en rekke tester, er det for eksempel generelt tilstrekkelig.
|
1
2
3
4
5
6
8
9
|
For at dette skal fungere, må tabellen ikke haTEXT, NTEXT, IMAGE or CURSOR (eller enSQL_VARIANTmed noen av disse typene) som grunntype. I dag er dette stadig sjeldnere, men hvis du har noen form for komplikasjon, kan du tvinge en kolonne med en av de ikke-støttede typene til en støttet type. I praksis bruker jeg vanligvis en rutine som sjekker metadataene og gjør dette automatisk, men det er ikke pent.
i en fungerende versjon vil du sannsynligvis ønske å spesifisere listen over kolonner, spesielt hvis du må gjøre en eksplisitt tvang av datatyper, eller hvis du bare sjekker bestemte kolonner,
Verken BINARY_CHECKSUM() eller dens vanlige søster CHECKSUM() er helt nøyaktige for å fortelle deg om noe har endret seg på rad eller tabell. Vi viser dette ved å se på de vanlige ordene i det engelske språket, som finnes i et bord som heter CommonWords.. Du forventer at de alle skal ha et annet sjekksum, men det er ikke tilfelle.
|
1
2
3
4
5
6
8
9
|
… Å gi resultatet …
Bevæpnet med denne informasjonen, kan vi raskt vise at forskjellige strenger kan ha samme kontrollsum
|
1
2
3
|
All these will; have the same checksum, as would …
|
1
2
3
|
SELECT
BINARY_CHECKSUM(‘accosted guards’),
BINARY_CHECKSUM(‘accorded feasts’)
|
….mens…
|
1
2
3
|
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(‘This looks very much CHECKSUM (‘dette ser veldig mye ut som den neste’),
CHECKSUM (‘Dette ser veldig mye Ut Som Den Neste’)
|
… finner dem å være alle de samme, fordi den bruker gjeldende sortering og min sortering for databasen er små bokstaver. CHECKSUM() tar sikte på å finne strenger like i sjekksum hvis de er like i en streng sammenligning.
|
2
|
så det beste du kan si er at det er stor sannsynlighet for at tabellene vil være de samme, men hvis du trenger å være helt sikker, bruk en annen algoritme.
hvis du ikke har noe imot forskjell i tilfelle i tekststrenger, kan du bruke CHECKSUM() i stedet for BINARY_CHECKSUM()
den store verdien av denne teknikken er at når du har beregnet sjekksummen du trenger, kan du lagre den som en verdi i kolonnen i et bord i stedet for å trenge det opprinnelige bordet, og derfor kan du gjøre hele prosessen selv raskere og kortere tid. Hvis du lagrer checksumverdien returnert av CHECKSUM() sørg for at du sjekker mot live-tabellen med et checksum generert med samme sortering.
her er et enkelt eksempel på ‘hva er endret’ rutine.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
…which gives…
|
1
2
3
|
au_id au_lname au_fname phone address city state zip contract
———– ——— ——— ———— ————— ————- —– —– ——–
327-89-2366 Mendoza Arthur 529275-5757 15 Hague Blvd. Liten Stein av 98949 1
|
Og så rydder vi bare opp.
|
1
2
3
|
selvfølgelig kan du bruke en utløser, men noen ganger vil du kanskje bare ha en daglig eller ukentlig rapport om endringer uten inntrenging av en utløser i et bord.
BRUKE XML
en generell mulighet er å sammenligne XML-versjonen av de to tabellene, siden dette gjør datatypen oversettelse til strenger for deg. Det er tregere enn Sjekksummen tilnærming, men mer pålitelig.
|
1
2
3
4
5
6
7
8
9
10
|
IF CONVERT(VARCHAR(MAX),(
SELECT *
FROM authors ORDER BY au_id FOR XML path, root))
=
CONVERT(VARCHAR(MAX),(
SELECT *
FROM authorscopy ORDER BY au_id FOR XMLpath, root))
SELECT ‘they are the same’
ELSE
VELG ‘de er forskjellige’
|
her kan du spesifisere typen sammenligning ved å spesifisere sorteringen.
eller du kan gjøre dette ved å sammenligne data i tabeller ..
|
1
2
3
4
5
6
7
8
9
10
|
IF BINARY_CHECKSUM(CONVERT(VARCHAR(MAX),(
SELECT *
FROM authors ORDER BY au_id FOR XML path, root)))
=
BINARY_CHECKSUM (CONVERT(VARCHAR(MAX),(
SELECT *
FROM authorscopy ORDER BY au_id FOR XML path, root)))
VELG ‘DE er ganske mye det samme’
ELSE
VELG ‘DE er forskjellige’ VELG ‘DE er forskjellige’
|
… ved å beregne en sjekksum AV XML-versjonen av tabellen. Dette lar deg lagre sjekksummen til tabellen du sammenligner med.
Finne hvor forskjellene er i en tabell
den enkleste oppgaven er hvor tabellene har et identisk antall rader og en identisk tabellstruktur. Noen ganger vil du vite hvilke rader som er forskjellige, og som mangler. Du må selvfølgelig angi hva du mener med ‘det samme’, spesielt hvis de to tabellene har forskjellige kolonner. Metoden du velger å gjøre sammenligningen, bestemmes vanligvis av disse detaljene.
UNION ALL … – GRUPPEN etter teknikk
den klassiske tilnærmingen til å sammenligne tabeller er å bruke en UNION ALL forSELECT setninger som inneholder kolonnene du vil sammenligne ,og deretter GROUP BY disse kolonnene. Åpenbart, for at dette skal fungere, må det være en kolonne med unike verdier i GROUP BY, og primærnøkkelen er ideell for dette. Verken tabellen er tillatt duplikater. Hvis de har forskjellig antall rader, vil disse vises som forskjeller.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
velg distinkt au_id
fra
(
velg au_id, au_lname, au_fname, telefon, adresse, by, stat, zip, kontrakt
fra forfattere
union alle
VELG au_id, au_lname, au_fname, telefon, adresse, by, stat, zip, kontrakt
fra forfatterscopy) BothOfEm
GRUPPE av au_id, au_lname, au_fname, telefon, adresse, by, stat, zip, kontrakt
HAR TELLER(*)<2) f
|
hvis en av tabellene HAR et duplikat, vil det gi deg et falskt resultat, som her, hvor du har to tabeller som er veldig forskjellige og resultatet forteller deg at de er de samme! Av denne grunn er det en god ide å inkludere kolonnen(e) som utgjør primærnøkkelen, og bare inkludere radene en gang!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
23
|
… giving …
|
1
2
3
4
|
teknikken kan brukes til å sammenligne flere enn to tabeller. Du trenger bare å UNION ALL tabellene du må sammenligne og endre HAVING – klausulen for å filtrere bare radene som ikke er i alle tabellene.
BRUKE UNNTATT
du kan nå bruke mye renere og litt raskereEXCEPT.
|
1
2
3
|
dette viser alle radene i forfattere som ikke finnes i authorscopy. Hvis de er de samme, vil det ikke returnere noen rader
|
1
2
3
4
6
7
8
9
10
11
12
13
14
|
jeg bruker bare select * for å holde ting enkelt for artikkelen. Du vil normalt spesifisere alle kolonnene du vil sammenligne.
dette vil bare fungere for tabeller med samme antall rader fordi, hvis forfattere hadde ekstra rader, ville det fortsatt si at de var forskjellige siden radene I Forfattere som ikke var i authorsCopy, ville bli returnert. Dette er fordi EXCEPT returnerer noen distinkte verdier fra spørringen til venstre for EXCEPT operand som ikke også finnes fra spørringen til høyre
dette viser forhåpentligvis hva jeg mener
|
1
3
4
5
6/div>
7
8
9
10
11
12
13
|
…yields …
|
1
2
3
4
|
Address_ID TheAddress ThePostCode
———– ———————————————- ———–
(0 rader)
|
…mens …
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
velg Adresse_id,Adressen,Postkoden FRA
(VERDIER
(8, «‘The Pippins», 20 Gloucester Pl, Chirton Ward, Tyne & Bruk UK’,’NE29 7AD’),
(8, «‘The Pippins», 20 Gloucester Pl, Chirton Ward, Tyne
& bruk UK’,’ne29 7ad’), (9, ‘929 Augustine Lane, Staple Hill ward south gloucestershire uk’,’bs16 4ll’),
(10, ’45 bradfield road, parwich derbyshire uk’,’de6 1qn’)
) Tabell(Adresse_id,
unntatt
velg address_id,theaddress,thepostcode
FRA
(VERDIER
(9, ‘929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich DERBYSHIRE UK’,’DE6 1QN’)
) TableA(Adresse_id,Adresse,Postkode)
|
..results in …
|
1
2
3
4
5
|
Address_ID TheAddress ThePostCode
———– ————————————————————- ———–
8 ‘The Pippins’, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK NE29 7AD
(1 row(s) affected)
|
denne funksjonen i EXCEPT kan brukes til fordel hvis du spesielt ønsker å sjekke at TableA finnes i TableB. Så hvor tabellene har et annet antall rader, kan du fortsatt sammenligne dem.
du vil kanskje ikke sammenligne alle kolonnene. Du bør alltid angi de kolonnene du ønsker å sammenligne for å bestemme ‘likhet’. Hvis Du bare vil sammenligne Adressen for eksempel, vil du bruke …
|
1
2
3
|
den ytre sammenføyningsteknikken
det er også teknikken til den ytre sammenføyningen. Dette er en mer generell teknikk som gir deg ekstra fasiliteter. Hvis du for eksempel bruker den fulle ytre sammenføyningen, kan du få de uovertruffen radene i begge tabellene. Dette gir deg en ‘før’ og ‘etter’ visning av endringer i dataene. Den brukes mer generelt i synkronisering for å fortelle deg hvilke rader som skal slettes, settes inn og oppdateres.
We’ll just use the technique to get the altered rows in authorsCopy
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT authors.au_id, authors.au_lname, authors.au_fname, authors.phone, authors.address, authors.city, authors.state, authors.zip, authors.på authors.au_ID = AuthorsCopy.au_ID
og authors.au_lname =authorsCopy.au_lname og authors.au_fname =authorsCopy.au_fname
og authors. au_fname = authorsCopy. au_fname
OG authors. au_fname = authorsCopy. au_fname
OG authors. au_fname
OG authors. au_fname
OG authors. au_fname
OG authors. au_fname
telefon =authorsCopy.telefon
OG COALESCE (forfattere.adresse,»)=COALESCE(authorsCopy.adresse,»)
OG COALESCE (forfattere.by,») = COALESCE(forfatterscopy.by,»)
OG COALESCE (forfattere.stat,») =COALESCE(authorsCopy.stat,»)
OG COALESCE (forfattere.zip,») =COALESCE (authorsCopy.zip,»)
og forfattere.kontrakt =authorsCopy.der authorsCopy.au_ID ER NULL
|
som du kan se, er det problemer med null kolonner med denne tilnærmingen, men det er like fort som de andre, og det gir deg litt mer allsidighet for sammenligningene dine.
Finne forskjellene mellom tabellene
du kan trenge en rask måte å se hvilken kolonne og rad som er endret. En veldig genial måte å gjøre dette på ble publisert nylig. DET brukes XML. ‘Sammenlign Tabeller Og Rapporter Forskjellene Ved Å Bruke Xml Til Å Pivotere Dataene ‘(redaktørens merknad: kobling utdatert). Det er smart, men for sakte. Det samme kan gjøres rent I SQL. I utgangspunktet utfører du en kolonne for kolonne sammenligning av data basert på primærnøkkelen, ved hjelp av et nøkkel / verdipar. Hvis du gjør hele bordet på en gang, er det ganske sakte: det beste trikset er å gjøre dette bare på de radene der du vet at det er forskjell.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57/div>
58
59
60
61
62
63
64
|
i vårt eksempel vil dette gi:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
denne teknikken roterer radene i tabellene som har forskjeller i EN Entity-attribute-value (EAV) – tabell slik at forskjeller i en rad kan sammenlignes og vises. Det gjør denne rotasjonen ved UNION ing navnet og strengverdien til hver kolonne. Denne teknikken fungerer best der det ikke er et stort antall forskjeller.
Konklusjoner
det er ingen enkelt ideell metode for å sammenligne dataene i tabeller eller resultater. En av en rekke teknikker vil være den mest relevante for en bestemt oppgave. Det er helt ned til nøyaktig de svarene du trenger og typen oppgave. Trenger du en rask sjekk at et bord ikke har endret seg,eller trenger du å vite nøyaktig hva endringene er? SQL er naturlig rask til å gjøre denne oppgaven, og sammenligninger av tabeller og resultater er en kjent oppgave for mange databaseutviklere.Hvis det er en generell regel, vil jeg si at utforskende eller ad hoc-arbeid trenger et verktøy som SQL Data Compare, mens en rutinemessig prosess i databasen krever en HÅNDSKÅRET SQL-teknikk.