Redgate Hub
introduktion
det finns flera anledningar till varför du kan behöva jämföra tabeller eller resultat.
- ibland behöver man bara veta om tabellerna innehåller data som är samma eller olika; Inga detaljer: bara ja eller nej. Detta är typiskt med testkrav, där du bara behöver veta om din rutin eller sats ger ett resultat med rätt data i den. när den är försedd med särskilda valar för parametrarna. Det är antingen fel eller rätt
- ibland måste du veta vilka rader som har ändrats utan att kanske vara specifika om vilka kolumner som ändrats och hur.
- Det finns tillfällen när du har ett stort bord i termer av både kolumner och rader, och du behöver något som visar dig specifikt kolumnen / kolumnerna som ändrade deras värde. Du kanske också vill ha detta när du spårar ner ett fel i en rutin som annars kan kräva att du slösar bort tidssökning ’med ögat’.
Vi kommer att ta itu med dessa tre ganska olika uppgifter i SQL
om två tabeller har ett annat antal rader kan de naturligtvis inte vara desamma. Det finns dock tillfällen när du behöver veta om Table_B innehåller alla rader av Table_A, utan skillnader. Om du vill ha mer detaljer kanske du till och med vill veta raderna i endera tabellen som inte är gemensamma eller de vanliga raderna, som indikeras av primärnyckeln, som var olika. Varför hålla fast vid att jämföra bara två tabeller? Det finns sätt att jämföra så många du behöver. (som till exempel när du jämför metadata i flera databasbilder). Ja, det finns många variationer
du har verktyg och funktioner för att göra det här, säkert?
det finns alltid en plats för verktyg som SQL Data Compare, TableDiff, tSQLt eller Change Data Capture. Mycket beror på omständigheterna och typen av uppgift. Problemet med att göra revisioner av ändringar av data i ett levande system är ett separat ämne, liksom synkroniseringen av tabeller och databaser. Jämförelse av XML-dokument är också utanför tillämpningsområdet. Vi kommer att hantera rent rutinmässig jämförelse av data i tabeller
Jag är mest sannolikt att använda TSQL-tekniker för att jämföra tabeller när:
utveckla…
under utvecklingen av en databas jämförs många tabeller. Det är inte bara de stora grejerna: varje tabellvärderad funktion behöver till exempel en testsele i byggskriptet som ser till att det gör vad du tycker att det borde göra under alla tänkbara testförhållanden och innehåller alla otäcka kantfall där det har fångats av testarna tidigare. Varje lagrad procedur behöver ett test för att se till att processen som den utför gör exakt vad som är avsett och inget annat.
det var en tid då byggaktiviteten var ganska lugn, men när du har ett nattligt Bygg-och integrationstest är det bäst att automatisera det helt och bli av med sysslan.
ETL
När du automatiserar laddningen av data i ett system måste du ofta testa olika förhållanden. Behöver du uppdatera befintliga versioner av raderna samt infoga de nya? Behöver du en fälla för att förhindra dubbla poster, eller ens ta bort befintliga poster?
ställa in testdata.
skripten i den här artikeln använder alla en tabell från den ärafulla PUBS-databasen. Vi kommer att använda författartabellen, men kommer att öka antalet rader lite till 5000 för att få en storlek som är lite mer realistisk. Jag har gett källan till tabellen med artikeln.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy Lägg till begränsning PK_AUTHORSCOPY primärnyckel grupperad
(au_id) på primär
gå
|
och ändrade sedan några av raderna.
|
1
2
3
4
5
|
uppdatera authorscopy set address=Stuff(adress,1,1,”)
där au_id in (
välj topp 10 au_id
från authorscopy f
Beställ via telefon)
|
så nu bör de två tabellerna vara övervägande desamma med några mindre ändringar i adressfältet
testning för att se om tabellerna är olika.
Ibland vill du bara veta om tabellerna är desamma. Ett exempel på detta skulle vara att kontrollera att en TVF fungerar korrekt genom att jämföra resultatet med ett befintligt bord med rätt resultat. Det vanliga sättet att göra detta är med CHECKSUM()grupp av funktioner i SQL Server, eftersom de är mycket snabba.
använda kontrollsummor
Du kan använda funktionenBINARY_CHECKSUM för att kontrollera om tabellerna är desamma: ja, ungefär samma. Det är snabbt, men det är inte perfekt, som jag ska visa på ett ögonblick. Om du har en serie tester är det till exempel i allmänhet tillräckligt.
|
1
2
3
4
5
6
7
8
9
|
om (
välj checksum_agg(binary_checksum(*))
från författarna)=(
välj checksum_agg(binary_checksum(*))
från authorscopy)
välj ’de är förmodligen samma’
annars
välj ’de är olika’
|
för att detta ska fungera måste tabellen inte ha TEXT, NTEXT, IMAGE or CURSOR (eller ett SQL_VARIANT med någon av dessa typer) som Bastyp. Numera är detta alltmer sällsynt, men om du har någon form av komplikation kan du tvinga någon kolumn med en av de typer som inte stöds till en typ som stöds. I praktiken använder jag vanligtvis en rutin som kontrollerar metadata och gör det automatiskt, men det är inte vackert.
i en fungerande version skulle du förmodligen vilja ange listan över kolumner, särskilt om du måste göra en uttrycklig tvång av datatyper, eller om du bara kontrollerar vissa kolumner,
varken BINARY_CHECKSUM() eller dess vanliga syster CHECKSUM() är helt korrekta när du berättar om något har ändrats i en rad eller tabell. Vi visar detta genom att titta på de vanliga orden i det engelska språket, som finns i en tabell som heter CommonWords.. Du förväntar dig att de alla har en annan kontrollsumma, men det är inte fallet.
|
1
2
3
4
5
6
7
8
9
|
välj sträng, binary_checksum(sträng) som ”kontrollsumma”
från commonwords
där binary_checksum(sträng) i
(
välj binary_checksum(sträng)
från commonwords
grupp av binary_checksum(sträng)
har count(*) > 2)
ORDER BY BINARY_CHECKSUM(sträng)
|
… vilket ger resultatet …
beväpnad med denna information kan vi snabbt visa att olika strängar kan ha samma kontrollsumma
|
1
2
3
|
välj binary_checksum(’Reed nerden’),
binary_checksum(’stämde boet’),
BINARY_CHECKSUM(’stud the oust’)
|
All these will; have the same checksum, as would …
|
1
2
3
|
SELECT
BINARY_CHECKSUM(’accosted guards’),
BINARY_CHECKSUM(’accorded feasts’)
|
….medan…
|
1
2
3
|
välj binary_checksum(’detta ser väldigt mycket ut som nästa’),
binary_checksum(’det här ser väldigt mycket ut som nästa’),
binary_checksum(’det här ser väldigt mycket ut som nästa’)
|
… ger dig olika kontrollsummor så här…
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(’This looks very much som nästa’),
CHECKSUM(’det här ser väldigt mycket ut som nästa’),
… finner att de är alla samma, eftersom det använder den aktuella sorteringen och min röst är en av de mest populära och mest populära. |
… hittar dem att vara alla samma, eftersom det använder den aktuella sorteringen och min sortering för databasen är skiftlägeskänslig. CHECKSUM() syftar till att hitta strängar lika i kontrollsumma om de är lika i en strängjämförelse.
|
1
2
|
———– ———– ———–
-943581052 -943581052 -943581052
|
så det bästa du kan säga är att det finns en stor sannolikhet att tabellerna blir desamma men om du behöver vara helt säker, använd sedan en annan algoritm.
Om du inte har något emot skillnad i fall i textsträngar kan du använda CHECKSUM() istället för BINARY_CHECKSUM()
det stora värdet av denna teknik är att när du har beräknat kontrollsumman som du behöver kan du lagra det som ett värde i kolumnen i en tabell istället för att behöva den ursprungliga tabellen och därför kan du göra hela processen till och med snabbare och tar mindre tid. Om du lagrar kontrollsummans värde som returneras av CHECKSUM() kontrollera att du kontrollerar mot Live-tabellen med en kontrollsumma genererad med samma sortering.
Här är ett enkelt exempel på’ vad har förändrats ’ rutin.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
– vi skapar en ”kontrollsumma” – tabell ”i farten” med select into.
välj
au_ID,
BINARY_CHECKSUM(au_id, au_lname, au_fname, telefon, , stad, , zip, ) som
i auchk
från authorscopy
Beställ av au_ID
/ * nu lägger vi in en begränsning bara för att kontrollera att vi inte har vunnit lotteriet (mycket osannolikt men inte helt omöjligt att vi har två rader med samma kontrollsumma) * /
Alter Table AUCHK Lägg till begränsning isitunique unique ()
uppdatera authorscopy set au_fname=’Arthur’
där au_id=’327-89-2366′
välj authorscopy.*
FROM authorscopy
INNER JOIN AuChk ON authorscopy.au_ID=AuChk.au_ID
WHERE <>BINARY_CHECKSUM(authorscopy.au_id, au_lname, au_fname, phone, , city, , zip, )
|
…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 sten av 98949 1
|
och sedan städar vi bara upp.
|
1
2
3
|
/* och vi bara pop det tillbaka till vad det var, som en del av teardown */
uppdatera authorscopy set au_fname=’Arnold’
där au_id=’327-89-2366′
|
naturligtvis kan du använda en utlösare men ibland kanske du bara vill ha en daglig eller veckovis rapport om ändringar utan att en utlösare tränger in i en tabell.
använda XML
en allmän möjlighet är att jämföra XML-versionen av de två tabellerna, eftersom detta gör datatypöversättningen till strängar åt dig. Det är långsammare än kontrollsumman men mer tillförlitlig.
|
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
välj ’de är olika’
|
Här kan du ange typ av jämförelse genom att ange sorteringen.
eller så kan du göra det genom att jämföra 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)))
välj’ de är ungefär samma ’
annars
välj ’de är olika’ välj ’de är olika’
|
… genom att beräkna en kontrollsumma för XML-versionen av tabellen. Detta gör att du kan lagra kontrollsumman för tabellen du jämför med.
hitta var skillnaderna finns i en tabell
den enklaste uppgiften är där tabellerna har ett identiskt antal rader och en identisk tabellstruktur. Ibland vill du veta vilka rader som är olika och vilka som saknas. Du måste naturligtvis ange vad du menar med ’samma’, särskilt om de två tabellerna har olika kolumner. Metoden du väljer att göra jämförelsen bestäms vanligtvis av dessa detaljer.
the UNION ALL … GROUP BY technique
det klassiska sättet att jämföra tabeller är att använda ettUNION ALL förSELECT uttalanden som innehåller kolumnerna du vill jämföra och sedanGROUP BY dessa kolumner. Självklart, för att detta ska fungera måste det finnas en kolumn med unika värden i GROUP BY, och primärnyckeln är idealisk för detta. Ingen av tabellerna är tillåtna dubbletter. Om de har olika antal rader visas dessa som skillnader.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
välj distinkt au_id
från
(
välj au_id
från
(
välj au_id, au_lname, au_fname, telefon, Adress, Stad, Stat, Zip, kontrakt
från författare
Union all välj au_id, au_lname, au_fname, telefon, Adress, Stad, Stat, zip, kontrakt
från authorsCopy) BothOfEm
grupp av au_id, au_lname, au_fname, telefon, Adress, Stad, Stat, zip, kontrakt
med COUNT (*) < 2) f
|
om en av tabellerna har en dubblett, kommer det att ge dig ett falskt resultat, som här, där du har två tabeller som är väldigt olika och resultatet säger att de är desamma! Av denna anledning är det bra att inkludera kolumnen / kolumnerna som utgör primärnyckeln och bara inkludera raderna en gång!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
välj count(*), address_id,theaddress,thepostcode
från
(
välj Adress_id,adress,Postkod
från
(
värden
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’,’DE6 1QN’)
) Tablea(Address_id,theaddress,thepostcode)
Union alla
välj ADDRESS_ID,theaddress,thepostcode
från
(
värden
(8, ”’the Pippins”, 20 Gloucester PL, CHIRTON Ward, Tyne & Wear UK’,’NE29 7AD’),
(8, ”’The Pippins”, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK’,’NE29 7AD’),
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’,’DE6 1QN’)
) TableB(Address_ID,TheAddress,ThePostCode)
)f
GROUP BY Address_ID,TheAddress,ThePostCode
HAVING COUNT(*)<2
|
… giving …
|
1
2
3
4
|
TheCount Address_ID TheAddress ThePostCode
———– ———– ————————- ————
(0 rad(er) påverkas)
|
tekniken kan användas för att jämföra mer än två tabeller. Du behöver bara UNION ALL tabellerna du behöver jämföra och ändra HAVING – klausulen för att filtrera bara raderna som inte finns i alla tabeller.
använda utom
Du kan nu använda mycket renare och något snabbare EXCEPT.
|
1
2
3
|
välj * från författare
utom
välj * från författarskopiering
|
detta visar alla rader i författare som inte finns i authorscopy. Om de är samma, skulle det returnera inga rader
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
au_id au_lname au_fname telefonadress stadstat zip-kontrakt
———– ———– ——— ———— ————————– ———– —– —– ——–
041-76-1076 Sosa Sonja 000-198-8753 29 Andra Avenyn Omaha CT 23243 0
187-42-2491 Mc Connell Trenton 0003090766 279 Haag sätt San Diego NY 94940 1
220-43-7067 Fox Judith 000-137-9418 269 östra Haag Street Richmond VA 55027 0
505-28-2848 hardy Mitchell 001-2479822 73 grön Milton enhet Norfolk wa 69949 1
697-84-0401 Montes Leanne 000-018-0454 441 East Oak Parkway San Antonio md 38169 1
727-35-9948 lång Jonathon 000-8761152 280 Nobel Avenue Anchorage la null 1
875-54-8676 sten Keisha 000-107-1947 763 Vit Fabien sätt Fremont ND 08520 0
884-64-5876 Keller Steven 000-2787554 45 Vit Nobel Boulevard Milwaukee NY 29108 1
886-75-9197 Ellis Marie 001032-5109 35 East Second Boulevard Chicago IL 32390 1
975-80-3567 Salazar Johanna 001-028-0716 17 New Boulevard Jackson nd 71625 0
(10 rad(er) påverkas)
|
jag använder bara select * för att hålla saker enkelt för artikeln. Du specificerar normalt alla kolumner du vill jämföra.
detta fungerar bara för tabeller med samma antal rader eftersom, om författare hade extra rader, skulle det fortfarande säga att de var olika eftersom raderna i Författare som inte fanns i authorsCopy skulle returneras. Detta beror på att EXCEPT returnerar några distinkta värden från frågan till vänster om EXCEPT operand som inte också hittas från frågan till höger
detta visar förhoppningsvis vad jag menar
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
Välj Adress_id,adressadress,Postkod
från
(värden
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’,’DE6 1QN’)
) TableA(ADDRESS_ID,TheAddress,ThePostCode)
utom
välj ADDRESS_ID,theaddress,thepostcode från
(värden
(8, ”’the Pippins”, 20 Gloucester PL, chirton Ward, Tyne & Wear UK’,’ne29 7AD’),
(8, ”’Pippins”, 20 Gloucester PL, chirton Ward, Tyne & Wear UK’,’NE29 7AD’),
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’,’DE6 1QN’)
) TableB(Address_ID,TheAddress,ThePostCode)
|
…yields …
|
1
2
3
4
|
Address_ID TheAddress ThePostCode
———– ———————————————- ———–
(0 rad(er) påverkas)
|
…medan …
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
välj Adress_id,adress,Postkod från
(värden
(8, ”’Pippins”, 20 Gloucester Pl, Chirton Ward, Tyne & Wear UK’,’NE29 7AD’),
(8, ”’Pippins”, 20 Gloucester PL, Chirton Ward, Tyne & Wear UK’,’ne29 7AD’),
(9, ’929 Augustine Lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield Road, parwich Derbyshire UK’,’DE6 1QN’)
) Tableb(Address_ID,TheAddress,ThePostCode)
utom
välj address_id,Theaddress,Thepostcode
från
(värden
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,’BS16 4LL’),
(10, ’45 Bradfield road, Parwich Derbyshire UK’,’DE6 1QN’)
) TableA(Address_ID,TheAddress,ThePostCode)
|
..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)
|
den här funktionen i EXCEPTkan användas till fördel om du särskilt vill kontrollera att TableAfinns i TableB. Så där tabellerna har ett annat antal rader kan du fortfarande jämföra dem.
Du kanske inte vill jämföra alla kolumner. Du bör alltid ange de kolumner du vill jämföra för att bestämma ’likhet’. Om du bara ville jämföra adressen till exempel skulle du använda …
|
1
2
3
|
välj adress från författare
utom
Välj adress från författarekopiera
|
den yttre kopplingstekniken
det finns också tekniken för den yttre anslutningen. Detta är en mer allmän teknik som ger dig ytterligare faciliteter. Om du till exempel använder den fullständiga yttre kopplingen kan du få de oöverträffade raderna i endera tabellen. Detta ger dig en’ före ’och’ efter ’ vy av förändringar i data. Det används mer allmänt i synkronisering för att berätta vilka rader som ska raderas, infoga och uppdatera.
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.kontrakt
från författare
vänster yttre gå med i authorsCopy
på författare. au_ID = AuthorsCopy.au_ID
och författare.au_lname =authorsCopy.au_lname
och författare.au_fname =authorsCopy.au_fname
och författare.telefon =authorsCopy.telefon
och COALESCE(författare.adress,”)=COALESCE(authorsCopy.adress,”)
och COALESCE(författare.stad,”) =COALESCE(authorsCopy.stad,”)
och COALESCE(författare.state,”) =COALESCE(authorsCopy.stat,”)
och COALESCE(författare.zip,”) =COALESCE(authorsCopy.zip,”)
och författare.kontrakt =authorsCopy.kontrakt
där authorsCopy. au_ID är NULL
|
som du kan se finns det svårigheter med null-kolumner med detta tillvägagångssätt, men det är lika snabbt som de andra och det ger dig ganska mer mångsidighet för dina jämförelser.
hitta skillnaderna mellan tabeller
Du kan behöva ett snabbt sätt att se vilken kolumn och rad som har ändrats. Ett mycket genialt sätt att göra detta publicerades nyligen. Det använde XML. ’Jämför tabeller och rapportera skillnaderna genom att använda Xml för att svänga Data’ (Redaktörens anmärkning: länk föråldrad). Det är smart, men för långsamt. Samma sak kan göras rent i SQL. I grund och botten utför du en kolumn för kolumnjämförelse av data baserat på primärnyckeln med ett nyckel/värdepar. Om du gör hela bordet på en gång är det ganska långsamt: det bästa tricket är att göra detta bara på de rader där du vet att det finns en skillnad.
|
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
53
54
55
56
57
58
59
60
61
62
64
|
i vårt exempel skulle detta ge:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
denna teknik roterar raderna i tabellerna som har skillnader i en Entity-attribute-value (EAV) tabell så att skillnader inom en rad kan jämföras och visas. Det gör denna rotation med UNION ing namn och strängvärde för varje kolumn. Denna teknik fungerar bäst där det inte finns ett stort antal skillnader.
slutsatser
det finns ingen enda idealisk metod för att jämföra data i tabeller eller resultat. En av ett antal tekniker kommer att vara den mest relevanta för en viss uppgift. Det handlar om exakt de svar du behöver och typen av uppgift. Behöver du en snabb kontroll att en tabell inte har ändrats, eller behöver du veta exakt vad ändringarna är? SQL är naturligtvis snabb på att göra denna uppgift och jämförelser av tabeller och resultat är en välbekant uppgift för många databasutvecklare.
om det finns en allmän regel skulle jag säga att utforskande eller ad hoc-arbete behöver ett verktyg som SQL Data Compare, medan en rutinprocess inom databasen kräver en handskuren SQL-teknik.