Redgate Hub
Bevezetés
táblázatok vagy eredmények összehasonlítása során számos oka lehet annak, hogy miért kell összehasonlítania a táblákat vagy az eredményeket.
- néha csak azt kell tudni, hogy a táblázatok azonos vagy eltérő adatokat tartalmaznak-e; Nincs adat: csak igen vagy nem. Ez jellemző a teszt állításokkal, ahol csak tudnia kell, hogy a rutin vagy a tétel eredményt hoz-e a megfelelő adatokkal. ha a paraméterekhez külön szelepekkel van ellátva. Ez vagy rossz, vagy jobb
- alkalmanként tudnia kell, hogy milyen sorok változtak anélkül, hogy talán különös lenne arról, hogy mely oszlopok változtak és hogyan.
- vannak esetek, amikor mind az oszlopok, mind a sorok tekintetében nagy tábla van, és szükséged van valamire, ami kifejezetten megmutatja az oszlop(OK) t, amelyek megváltoztatták értéküket. Lehet, hogy ezt is szeretné, ha egy olyan rutinban követne egy hibát, amely egyébként megkövetelheti, hogy pazarolja az időt a “szemmel” történő szkennelésre.
ezt a három meglehetősen különböző feladatot az SQL
– ben fogjuk kezelni, ha két tábla különböző számú sorral rendelkezik, akkor természetesen nem lehetnek azonosak. Vannak azonban olyan esetek, amikor tudnia kell, hogy a Table_B tartalmazza-e a Table_A összes sorát, különbségek nélkül. Ha szeretne részletesebben, akkor is szeretné tudni, hogy a sorok mindkét táblázatban, amelyek nem közös, vagy a közös sorok, amint azt az elsődleges kulcs, hogy más volt. Miért ragaszkodsz csak két asztal összehasonlításához? Vannak olyan módszerek, amelyek összehasonlítják annyi, amennyire szüksége van. (például, ha összehasonlítjuk a metaadatokat több adatbázis pillanatképek). Igen, sok variáció van
ehhez vannak eszközök és funkciók, biztosan?
mindig van hely olyan eszközökhöz, mint az SQL Data Compare, TableDiff, tSQLt vagy az adatrögzítés módosítása. Sok függ a körülményektől és a feladat típusától. Az élő rendszerben az adatváltozások ellenőrzésének problémája külön téma, csakúgy, mint a táblázatok, adatbázisok szinkronizálása. Az XML dokumentumok összehasonlítása szintén nem terjed ki. Kizárólag a
táblázatokban szereplő adatok rutinszerű összehasonlításával foglalkozunk, valószínűleg TSQL technikákat használok a táblázatok összehasonlítására, amikor:
…
egy adatbázis fejlesztése során sok táblát összehasonlítanak. Ez nem csak a nagy dolog: minden asztal értékes funkció, például, szüksége van egy teszt kábelköteg a build script, amely biztosítja, hogy nem, amit úgy gondolja, hogy meg kell tennie minden elképzelhető vizsgálati körülmények között, és magában foglalja az összes csúnya él esetekben, amikor már fogott a tesztelők a múltban. Minden tárolt eljárásnak szüksége van egy tesztre, hogy megbizonyosodjon arról, hogy az általa végrehajtott folyamat pontosan azt teszi, amit szándékoznak, semmi mást.
volt idő, hogy a build tevékenység meglehetősen kényelmes, de ha van egy éjszakai build and integration teszt, a legjobb, hogy automatizálják azt teljesen megszabadulni a házimunkát.
ETL
amikor automatizálja az adatok betöltését egy rendszerbe, gyakran különféle feltételeket kell tesztelnie. Frissítenie kell a sorok meglévő verzióit, valamint be kell illesztenie az újakat? Szüksége van egy csapdára, hogy megakadályozza a duplikált bejegyzéseket, vagy akár törölheti a meglévő bejegyzéseket?
A vizsgálati adatok beállítása.
Az ebben a cikkben szereplő szkriptek mindegyike egy táblázatot használ a tiszteletreméltó kocsmák adatbázisából. Fogjuk használni a szerzők táblázat, de marhahús fel a sorok számát egy kicsit 5000 annak érdekében, hogy a méret, hogy egy kicsit reálisabb. A cikkhez mellékeltem az asztal forrását.
I then created a copy of the table …
|
1
2
3
4
5
6
|
SELECT * INTO authorsCopy
FROM authors
GO
ALTER TABLE dbo.authorsCopy add CONSTRAINT PK_AUTHORSCOPY PRIMARY KEY CLUSTERED
(AU_ID) on PRIMARY
GO
|
, majd megváltozott néhány sor.
|
1
2
3
4
5
|
FRISSÍTÉS authorsCopy BEÁLLÍTOTT címet=CUCC(cím,1,1,”)
HOL au_ID A (
VÁLASSZA ki a TOP 10 au_id
A authorsCopy f
MEGRENDELÉS telefonon)
|
Szóval most a két táblát kell túlnyomórészt ugyanazt a néhány apróbb változások a cím mező
Vizsgálat látni, hogy a táblák eltérőek-e.
néha csak azt szeretné tudni, hogy a táblák azonosak-e. Ennek egyik példája annak ellenőrzése, hogy a TVF megfelelően működik-e, ha összehasonlítja annak eredményét egy meglévő táblázat eredményével a helyes eredményekkel. Ennek szokásos módja a CHECKSUM()funkciók csoportja az SQL Serverben, mert nagyon gyorsak.
A Checksums
segítségével a BINARY_CHECKSUM függvény segítségével ellenőrizheti, hogy a táblák azonosak-e: nos, nagyjából ugyanazok. Gyors, de nem tökéletes, amint azt egy pillanat alatt bemutatom. Ha van egy tesztsorozata, például általában elegendő.
|
1
2
3
4
5
6
7
8
9
|
E (
VÁLASSZA ki CHECKSUM_AGG(BINARY_CHECKSUM(*))
a szerzők)=(
VÁLASSZA ki CHECKSUM_AGG(BINARY_CHECKSUM(*))
A authorsCopy)
VÁLASSZA az ‘ők valószínűleg ugyanaz’
MÁS
VÁLASSZA ki a ‘más’
|
ahhoz, hogy ez működjön, a táblázatnak nem szabadTEXT, NTEXT, IMAGE or CURSOR (vagy aSQL_VARIANT ezen típusok bármelyikével) mint alaptípusa. Manapság ez egyre ritkább, de ha bármilyen szövődménye van, bármelyik oszlopot a nem támogatott típusok egyikével támogatott típusba kényszerítheti. A gyakorlatban általában olyan rutint használok, amely ellenőrzi a metaadatokat, és ezt automatikusan elvégzi,de ez nem szép.
egy működő változat valószínűleg azt szeretnéd, hogy adja meg a lista oszlopok, különösen akkor, ha a birtoklás-hoz csinál egy kifejezett korlátozás datatypes, vagy ha ellenőrzés csak bizonyos oszlopok,
, Sem BINARY_CHECKSUM() , sem a sima nővér CHECKSUM() teljesen pontos, hogy elmondja, ha valami megváltozott a sorban vagy asztalra. Ezt a CommonWordsnevű táblázatban szereplő Angol nyelv közös szavaival mutatjuk be.. Azt várnád, hogy mindegyiknek más ellenőrző összege legyen, de nem ez a helyzet.
|
1
2
3
4
5
6
7
8
9
|
VÁLASSZA ki a string, BINARY_CHECKSUM(string), MINT az “Ellenőrző”
A commonWords
HOL BINARY_CHECKSUM(string) A
(
VÁLASSZA ki BINARY_CHECKSUM(string)
A commonwords
CSOPORT ÁLTAL BINARY_CHECKSUM(string)
HAVING COUNT(*) > 2)
rendezés BINARY_CHECKSUM(string)
|
… ami az eredmény …
a Fegyveres ezt az információt, gyorsan bizonyítják, hogy a különböző szálakat is ugyanazok a checksum
|
1
2
3
|
VÁLASSZA ki BINARY_CHECKSUM (reed a kocka’),
BINARY_CHECKSUM(‘beperelte a fészek’),
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’)
|
….mivel…
|
1
2
3
|
VÁLASSZA ki BINARY_CHECKSUM(‘Ez nagyon hasonlít a következő’),
BINARY_CHECKSUM(‘ez nagyon hasonlít a következő’),
BINARY_CHECKSUM(‘Ez nagyon hasonlít a Következő’)
|
… ad különböző ellenőrzőösszegeket, mint ez…
|
1
2
|
———– ———– ———–
-447523377 -447522865 -447654449
|
The sister function CHECKSUM() …
|
1
2
3
4
|
SELECT CHECKSUM(‘This looks very much mint a következő’),
CHECKSUM(‘ez nagyon hasonlít a következő’),
CHECKSUM(‘Ez nagyon hasonlít a Következő’)
|
… megtalálja őket, hogy minden ugyanaz, mert használatával az aktuális leválogatással, valamint a leválogatással az adatbázis case-insensitive. CHECKSUM() célja, hogy az ellenőrző összegben azonos karakterláncokat találjon, ha azok egy karakterlánc-összehasonlításban egyenlőek.
|
1
2
|
———– ———– ———–
-943581052 -943581052 -943581052
|
Szóval, a legjobb, amit mondhatok, hogy nagy a valószínűsége, hogy a táblázatok ugyanaz lesz, de ha kell, teljesen bizonyos, akkor használjon egy másik algoritmus.
Ha nem bánod különbség, ha a szöveges karakterlánc, akkor használjuk a CHECKSUM() helyett BINARY_CHECKSUM()
A nagy érték, ezt a technikát, hogy, ha már számított a checksum, hogy szükség van, te képes tárolni, mint egy oszlop értékét a táblázat ahelyett, hogy szükségük van az eredeti tábla, ezért lehet, hogy az egész folyamat még gyorsabb, de kevesebb idő. Ha a CHECKSUM() által visszaadott ellenőrző összeget tárolja, győződjön meg róla, hogy ellenőrizze az élő táblázatot egy azonos kollekcióval létrehozott ellenőrző összeggel.
itt egy egyszerű példa a “mi változott” rutinra.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–majd hozzon létre egy ‘ellenőrző’ asztal ‘menet közben’ segítségével VÁLASSZA ki a BE.
válassza ki a
au_ID,
BINARY_CHECKSUM(AU_ID, au_lname, au_fname, telefon, , város, , zip,), mint
AUCHK
a autorscopy
megrendelését AU_ID
hogy nem nyertük meg a lottót (nagyon valószínűtlen, de nem teljesen lehetetlen, hogy két sorunk van ugyanazzal az ellenőrző összeggel) * /
ALTER TABLE AUCHK add constraint isitunique unique ()
update authorscopy set au_fname=’Arthur’
ahol au_id=’327-89-2366′
válassza a authorscopy lehetőséget.*
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. A 98949-es kis szikla 1
|
, majd csak rendezünk.
|
1
2
3
|
/* csak a pop vissza, hogy mi volt az, részeként a lepukkant */
FRISSÍTÉS authorscopy MEGHATÁROZOTT au_fname=’Arnold’
HOL au_ID=’327-89-2366′
|
persze jól jönne egy ravaszt, de néha lehet, hogy csak egy napi vagy heti jelentés a változásokat, anélkül, hogy a behatolás a ravaszt, egy asztal.
használata XML
egy általános lehetőség, hogy hasonlítsa össze az XML változata a két tábla, mivel ez nem a datatype fordítás Húrok az Ön számára. Lassabb, mint az ellenőrző összeg megközelítés, de megbízhatóbb.
|
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álassza ki a “különbözőek” lehetőséget
|
itt megadhatja az összehasonlítás típusát az összesítés megadásával.
vagy ezt megteheti, összehasonlítva a táblázatokban szereplő adatokat ..
|
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álassza ki az ‘ezek nagyjából ugyanaz’
ELSE
válassza ki az ‘különböznek’ SELECT ‘különböznek’
|
… a táblázat XML verziójának ellenőrzőösszegének kiszámításával. Ez lehetővé teszi az összehasonlított táblázat ellenőrző összegének tárolását.
annak megállapítása, hogy hol vannak a különbségek a
táblázatban, a legegyszerűbb feladat az, hogy a táblázatok azonos számú sorral és azonos táblázatszerkezettel rendelkezzenek. Néha azt szeretné tudni, hogy mely sorok különböznek egymástól, amelyek hiányoznak. Természetesen meg kell adnia, hogy mit jelent az “ugyanaz”, különösen akkor, ha a két tábla különböző oszlopokkal rendelkezik. Az összehasonlítás módját általában ezek a részletek határozzák meg.
az Unió minden … csoport technika
a táblázatok összehasonlításának klasszikus megközelítése a UNION ALL használata a SELECTállításokhoz, amelyek tartalmazzák az összehasonlítani kívánt oszlopokat, majd GROUP BY ezeket az oszlopokat. Nyilvánvaló, hogy ehhez egy oszlopnak kell lennie, amelynek egyedi értékei vannak a GROUP BY – ban, az elsődleges kulcs pedig ideális erre. Egyik táblázat sem engedélyezett másolatok. Ha különböző számú soruk van, ezek különbségekként jelennek meg.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
VÁLASSZA a KÜLÖNBÖZŐ au_ID
A
(
VÁLASSZA ki au_ID
a
(
VÁLASSZA ki au_id, au_lname, au_fname, telefon, cím, város, állam, irányítószám, szerződés
A szerzők
UNIÓ ÖSSZES
VÁLASSZA ki au_id, au_lname, au_fname, telefon, cím, város, állam, irányítószám, szerződés
A authorsCopy) BothOfEm
CSOPORT ÁLTAL au_id, au_lname, au_fname, telefon, cím, város, állam, irányítószám, szerződés
HAVING COUNT(*)<2) f
|
Ha az egyik asztal ismétlődő, akkor kapsz egy hamis eredményt, mint itt, ahol van, két asztalt, hogy nagyon más az eredmény azt mondja, hogy ezek ugyanazok! Ezért jó ötlet az elsődleges kulcsot alkotó oszlop(ok) beillesztése, és csak egyszer tartalmazza a sorokat!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT COUNT(*), Address_ID,TheAddress,ThePostCode
a
(
VÁLASSZA ki a Address_ID,TheAddress,ThePostCode
FROM
(
értékek
(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 all
select address_id,theaddress,thepostcode
(
értékek (8, “a Pippins”, 20 Gloucester PL, CHIRTON Ward, Tyne& amp; 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 sor(s) érintett)
|
A technika alkalmazható, összehasonlítva a több mint két asztal. Csak aUNION ALL össze kell hasonlítani és módosítani aHAVING záradékot, hogy csak azokat a sorokat szűrje, amelyek nem szerepelnek az összes táblában.
használata kivéve
most már használhatja a sokkal tisztább, valamivel gyorsabb EXCEPT.
|
1
2
3
|
select * from authors
Ez azt mutatja, az összes sort a szerzők, amelyek nem találhatók authorscopy. Ha egyformák, visszatér sor
csak a select * – ot használom, hogy a dolgok egyszerűek maradjanak a cikkhez. Általában elemezné az összes összehasonlítandó oszlopot. Ez csak akkor fog működni, a táblázatok, az azonos számú sor, mert, ha a szerzők volt extra sor, akkor mindig azt mondom, hogy voltak más, mivel a sorok a Szerzők, hogy nem authorsCopy vissza. Ez azért van, mert a Ez a, remélhetőleg megmutatja, mire gondolok
…yields …
…mivel …
..results in …
Ez a funkció a lehet, hogy nem szeretné összehasonlítani az összes oszlopot. Mindig meg kell adnia azokat az oszlopokat, amelyeket összehasonlítani szeretne, hogy meghatározza az “egységességet”. Ha csak össze akartam hasonlítani a Cím például használja …
A Külső Csatlakozz technikaOtt is a technika, a külső csatlakozni. Ez egy általánosabb technika, amely további létesítményeket biztosít. Ha például a teljes külső csatlakozást használja, akkor bármelyik táblázatban megkaphatja a páratlan sorokat. Ez egy “előtte” és “utána” képet ad az adatok változásairól. Általánosabban használják a szinkronizálás során, hogy megmondja, milyen sorokat kell törölni, beszúrni és frissíteni. We’ll just use the technique to get the altered rows in authorsCopy
mint látható, nehézségek vannak a null oszlopokkal ezzel a megközelítéssel, de olyan gyors, mint a többiek, és ez inkább sokoldalúságot ad az összehasonlításokhoz. Atáblázatok közötti különbségek megtalálásához gyors módja lehet annak, hogy megnézze, milyen oszlop és sor változott. Ennek nagyon ötletes módját a közelmúltban tették közzé. XML-t használt. “Táblázatok összehasonlítása és a különbségek jelentése az XML használatával az adatok elforgatásához” (a szerkesztő megjegyzése: link elavult). Okos, de túl lassú. Ugyanezt lehet tenni tisztán az SQL-ben. Alapvetően az adatok oszloponkénti összehasonlítását végzi az elsődleges kulcs alapján, kulcs / érték pár használatával. Ha az egész asztalt egyszerre csinálod, akkor meglehetősen lassú: a legjobb trükk az, hogy ezt csak azokon a sorokon végezzük, ahol tudod, hogy van különbség.
példánkban ez ad:
Ez a technika forog a sorok, táblák, amelyek a különbségeket egy Egyed-tulajdonság-érték (EK) asztalra úgy, hogy a különbségek belül sor lehet, mint jelenik meg. Ezt a forgást következtetéseknincs egyetlen ideális módszer az adatok táblázatokban vagy eredményekben történő összehasonlítására. Az egyik számos technika lesz a legfontosabb az adott feladat. Mindez pontosan a szükséges válaszokra és a feladat típusára vezethető vissza. Szüksége van egy gyors ellenőrzésre, hogy egy asztal nem változott-e, vagy pontosan tudnia kell, hogy mi a változás? Az SQL természetesen gyorsan végzi ezt a feladatot, a táblázatok és eredmények összehasonlítása pedig sok adatbázis-fejlesztő számára ismerős feladat. ha van egy általános szabály, azt mondanám, hogy a feltáró vagy ad-hoc munkának olyan eszközre van szüksége, mint az SQL Data Compare, míg az adatbázison belüli rutinfolyamat kézi vágású SQL technikát igényel. |