Förord

Författaren, Viktor Gorodnichenko, har inte bara skapat en väldigt användbar lagrad procedur för att jämföra databasens stuktur och data på en SQL Server, hans kod ger oss även en god insikt i hur man skriver avancerad Transact-SQL kod. Den här artikeln innehåller mycket av hur författaren tänkte när han byggde upp sp_CompareDB. Även om du inte alls förstår koden så kan du ändå använda denna kraftfulla stored procedure i ditt dagliga arbete. Instruktioner för att skapa och köra lagrade procedurer hittar du sist i denna artikel.

Innehåll

Ladda ned källkoden för den lagrade proceduren sp_CompareDB

Jag kan nästan slå vad om att alla databasadministratörer någon gång har haft ett desperat behov att kunna jämföra datan och strukturen mellan två tabeller, eller mellan två hela databaser för den delen. Skulle det inte var trevligt att ha en lagrad procedur som endast kräver två små parametrar - namnet på den första och namnet på den andra databasen - som jämför all data i dem, och presenterar skillnanderna mellan dem?

Medan det är relativt enkelt att jämföra stukturen i en databas eller tabell, både programmatiskt eller manuellt, så är det inte lika lätt att jämföra datan. Till skillnad mot att jämföra stukturer så är det ingen lätt uppgift att manuellt jämföra data. Jag påstår inte att ingen uppskattar det intellektuella arbetet med att jämföra tabell för tabell, kolumn för kolumn med flera JOIN- och NOT IN-satser i stora sql-förfrågningar. Vad jag påstår är att det är ofta en fysisk omöjlighet att lyckas om det inte handlar om en relativt liten mängd data, och misstag är oundvikliga.

Vad krävs för att skriva en sådan Lagrad Procedur?

Ja, låt oss jämföra strukturen och datan i två databaser programmatiskt. Om vi tänker lite på själv genomförandet, så kommer vi fram till en snabb lösning: gå igenom alla tabeller och skapa SELECT-satser för att erhålla skillnaderna. Men när vi börjar utföra detta rent praktiskt så stöter vi på en lång rad med "Hoppsan!" på vägen. Här kommer en lista på några av dessa som jag upptäckte när jag skapade min lagrade procedur för att jämföra data och strukturer i databaserna.

1. Tabeller kan innehålla en unik nyckel, men behöver inte göra det. Det är mycket enklare att hitta skillnaderna och visa dem om det finns en unik nyckel i tabellen. Min första tanke var att eftersom en tabell utan en unik nyckel är ett brott mot Första normalformen (1NF) vid normalisering av en databas så kunde jag bara hoppa över dessa tabeller i mitt script. Men, efter att ha testat min lagrade procedur för att jämföra olika databaser så kom jag fram till att det faktiskt finns många databaser som har tabeller helt utan unika nycklar, så min slutsats blev att det var bäst att inte ignorera dessa.
   
   Min lagrade procedur kollar i första hand efter en primär nyckel att jämföra med, och visa skillnaderna. Om en primär nyckel saknas, så används den första unika restriktionen eller den unika indexeringen.



2. Innan datan kan jämföras, så är det första steget att jämföra strukturen. Annars blir det som att jämföra äpplen med apelsiner. Om du har detta första steg så är det bättre att dra nytta av det och tydligt rapportera de strukturella skillnaderna. Min lagrade procedur visar alltså skillnader som:
   
   
   [kunder]
   Kolumnen telefon: i db1 - char(12), i db2 - char(14)
   [kalender]
   Kolumnen plats finns inte i db2
   
   Tabeller med olika strukturer jämför vi inte datan i. Om tabellen däremot innehåller kolumner av samma typ men med olika längd ( som telefon i exemplet här ovan ), eller om tabellerna är av kompatibla typer (har samma typ i syscolumns - char och nchar, varchar och nvarchar, etc.) så kan datan jämföras.



3. Datatyperna text, ntext och image kan man inte använda som predikat, och datan kan alltså inte jämföras direkt som med andra fält. men det är faktiskt lätt komma runt. Du kan använda:
   
   
SUBSTRING([TextColumnName],1,DATALENGTH([TextColumnName]))

   i predikat för text and ntext data och bara:
   
   
DATALENGTH([ImageColumnName])

   för image data. För om datan i ett image-fält har ändrats så har med all säkerhet dess längd ändrats också.



4. Somliga kolumner i vissa tabeller har värdet null. Även om du sätter ANSI_NULLS till OFF, så får du inte TRUE när du jämför två null-värden. Det är möjligt om du jämför två lokala variabler eller en tabells kolumnvärde med ett lokalt värde, men om du jämför två kolumner som har null så kommer du alltid att få svaret UNKNOWN. Hursomhelst så kan du inte använda detta SQL Server tillägg mot ANSI standarden (SET ANSI_NULLS OFF) om du utför en jämförelse då minst en av databaserna befinner sig på en länkad server. I det fallet måste du använda SET ANSI_NULLS, och SET ANSI_WARNINGS måste vara satt till ON.



5. Det är ganska uppenbart att de SELECT-satser som jämför datan måste exekveras på en dynamiskt basis, eftersom de skapas på vägen. Problemet är att vissa tabeller innehåller för många kolumner och att dessa kolumner ibland har väldigt långa namn. Jag föredrar självförklarande och tydliga namn, och jag blev en aning frustrerad då jag fick felmeddelandet "Felaktig syntax nära..." på grund av att SELECT-satsen blev större än 8000 bytes, som är max-storleken. Det fanns inget sätt att gå vidare, förutom att begränsa antalet kolumner att jämföra. Jag fick lov att använda mig av ett speciellt trick för att lösa detta.
   
   Jag sparade stängarna som innehöll alla jämförelser i en temporär tabell och sedan i ett dynamiskt script så deklarerade jag det antalet lokala variabler med typen varchar(8000) som behövdes för att få rum med alla strängar i den temporära tabellen. Sedan kunde jag exekvera den såhär:
   
   
EXEC (String1+String2+…)

   För det finns inga begränsningar för antalet sådanna strängar och därför fanns det inga begränsningar alls (förutom de som SQL Server sätter) på antalet kolumner eller längden på deras namn i de två databaserna som jämförs.



6. För att jämföra två databaser så måste minst en av dem använda 3-schema-arkitekturen i tabellerna, för du kan inte ha två aktuella databaser. Därför måste databasnamnet vara hårdkodat i scriptet. För att undvika detta och göra scriptet användarvänligt så kan vi använda en så kallad macro-ersättning och göra scriptet dynamiskt (d.v.s. att den lagrade proceduren är dynamisk T-SQL kod som i sig innehåller dynamisk T-SQL kod)



7. Jobbar du med SQL Server 2000 så kan du få problem med collations (eng.) när du jämför data. "Collation" är ett koncept som används för att identifiera språkuppsättningar (code pages) för Unicode och icke-Unicode data. En Collation bestämmer hur text hanteras, sorteras och jämförs internt för både Unicode och icke-Unicode datatyper vid t.ex. sökningar. Men med SQL Server 2000 kan du stöta på en oväntad och överraskande begränsning, som inte skulle vara ett problem med SQL Server 7.0.
   
   Låt oss säga att ditt företag gör program som använder sig av SQL Server 7.0 som databas. Du levererar databaser som innehåller Engelsk, Fransk och Tysk data till dina klienter. Allt detta passar perfekt i språkuppsättning 1252, dictionary sort order, case insensitive, accent sensitive. Alla klienter måste ha SQL servrar med samma inställning: 1252-CI-AS.
   
   Sen börjar du jobba med SQL Server 2000 istället. Nu kan dina klienter ha något av följande på sina servrar: Windows collation Latin1_General_CI_AS (för Tyska och Engelska), eller Windows collation French_CI_AS (för Franska), eller SQL Server collation SQL_Latin1_General_CP1_CI_AS. Detta innebär att databaserna som skapades med SQL Server 7.0 som skulle installeras både på SQL Server 7.0 och SQL Server 2000 ( med konvertering under upplägget) nu inte fungerar på servrar med Latin1_General_CI_AS eller French_CI_A. Du skulle få följande felmeddelande:
   
   
   Server: Msg 446, Level 16, State 9, Line 1
   Cannot resolve collation conflict for equal to operation.
   
   
   Du måste då antingen ha stöd för alla collations eller tvinga dina klienter att installera om SQL Server och tvinga dem att använda den collation du väljer. Det är inte helt fastställt varför fransk data i French_CI_AS och Latin1_General_CI_AS inte är kompatibla med varandra. Alla egenskaperna på collationerna är desamma:
   
   A. Språkuppsättningar for icke-Unicode data:
   French_CI_AS har 1252
   Latin1_General_CI_AS har 1252
   d.v.s. att bit mönstret för alla symboler som inte är Unicode är precis likadana.
   
   B. Sorteringsordningen för icke-Unicode data: Är likadana beroende på definitionen i collationen. Symbolordningen är precis densamma. Om du tvivlar så kan du du köra ett osofistikerat script för att hämta data från en tabell med två kolumner, där den ena kolumnen är Latin1_General_CI_AS och den andra i French_CI_AS. Använd ORDER BY Latin1_General kolumnen i en fråga, Franska i den andra och jämför sedan ordningen de visas i. Du märker att det inte finns några skillnader
   
   C. Språkuppsättningar för Unicode-data: Enligt SQL Server Books Online så "använder all Unicode-data samma Unicode teckenuppsättning. Collationer kontrollerar då inte teckenuppsättningen för Unicode kolumner, utan enbart attributer så som jämförelseregler och skillnad på gemener och versaler".
   
   D. Hur är det då med jämförelseregler och skillnaden mellan gemener och versaler? Kör:
   
   
SELECT COLLATIONPROPERTY ([CollationName],'ComparisonStyle')

   Resultatet blir: 196609 för både och. Det vill säga "case insensitive, accent sensitive, kana insensitive, width insensitive".
   
   Ännu märkligare är anledningen till varför datan i Latin1_General_CI_AS och SQL_Latin1_General_CP1_CI_AS inte är kompatibla. I detta fall är till och med utnämnaren av collationen (Latin1_General) lika för båda. Utför följande:
   
   
SELECT * FROM ::fn_helpcollations () WHERE name = 'Latin1_General_CI_AS' OR name = 'SQL_Latin1_General_CP1_CI_AS'

   för att se detta.
   
   Men, om du kör:
   
   
CREATE TABLE ChkCollate (
SQL_Latin1_General_Column char(10) COLLATE SQL_Latin1_General_CP1_CI_AS,
Latin1_General_Column char(10) COLLATE Latin1_General_CI_AS)

SELECT * FROM ChkCollate WHERE SQL_Latin1_General_Column = Latin1_General_Column

   så träffar vi vår gamla kompis igen:
   
   
   Server: Msg 446, Level 16, State 9, Line 1
   Cannot resolve collation conflict for equal to operation.
   
   Det är minst sagt märkligt att du kan framtvinga collationkompabilitet när du jobbar med två databaser på olika servrar om du kör följande för den länkade servern:
   
   
USE master
EXEC sp_serveroption 'LinkedServerName', 'collation compatible', 'true'

   medan du inte kan göra det när du jobbar med databaser på samma server.
   
   Slutsats: Databaserna du jämför måste ha samma collation (Samma utnämnaren (designator) av collationen räcker inte).
   


8. Prestandan är givetvis viktigt att ha i åtanke när du skriver ett komplicerat script. Jag misstänkte att min lagrade procedur inte skulle bli speciellt snabb, speciellt om det finns tabeller utan unika nycklar i en databas. Men jag ville få scriptet att fungera så snabbt som möjligt. Med prestandan i åtanke så valde jag att inte använda följande metod för att leta efter null-värden.
   
   
[db1.ColumnName] IS NULL AND [db2.ColumnName] IS NULL OR [db1.ColumnName] IS NOT NULL AND [db2.ColumnName] IS NOT NULL AND [db1.ColumnName]=[db2.ColumnName]

   Jag använde mig istället av följande:
   
   
ISNULL([db1.ColumnName],’!#null$’) = ISNULL([db2.ColumnName],’!#null$’)

   Jag gjorde det så för att SQL Server optimizer använder en "NESTED LOOP JOIN" i det första fallet och en "HASH JOIN" i det andra. Prestandaskillnaden är imponerande. I vissa tabeller är "NESTED LOOP" uppsättningen mellan 200 och 500 gånger långsammare. Du kan dock inte använda "HASH" i en sql-sträng med predikat-förfrågningar som i det första exemplet, då en SQL Server bara kan använda hash för utvärderingar.
   
   Det andra exemplet är en lösning från en prestandamässig synvinkel. Men medan den konstruktionen ser otroligt kort och fin ut enbart för text-baserade datatyper så kan vi kan inte använda detta för andra datatyper. Till exempel, om vi använder den för tinyint typen:
   
   
ISNULL([db1.TinyintColumnName],0)= ISNULL([db2.TinyintColumnName],0)

   så kommer den att uppfatta siffran noll och ett NULL värde som likadant, som ju absolut inte är sant! Men vad kan man då annars använda istället för 0? Tinyint har 256 möjliga värden och något av dessa skulle kunna vara i databasen. Datatypen bit är ännu mer begränsad med enbart två möjliga värden. Vi måste finna en annan lösning. Lösningen i det här fallet är att använda CASE. För att uppnå bra prestandalösning och för att få ett korrekt resultat, så måste vi behandla alla datatyper för sig. Vilka alternativ finns då för att jämföra alla tillängliga datatyper, och dessutom null-värden? Vi kan:
   
   
   
   1. Jämföra datan som den är om datatypen inte tillåter null-värden. Detta fungerar endast med datatypen timestamp.
   
   
   
   2. Använda ISNULL med ett ersättningsvärde med samma datatyp som kolumnen har, och då använda ett värde som praktiskt taget aldrig skulle användas som värde i en sådan kolumn. Ersättningsvärdet är då '!#null$'. Ett exempel på det hela:
      
      
ISNULL(d1.[CharCol],'!#null$')=ISNULL(d2.[CharCol],'!#null$')

   
   
   3. Använda CASE med ett ersättningsvärde (om det är null) med en likvärdig datatyp men med ett värde som praktiskt tagit aldrig skulle användas i datan. Vi kan inte använda ISNULL, för själva beskrivningen av kolumnen kan vara kort ( t.ex. så kan binary vara binary(1), så max för ersättningsvärdet är bara 0xff).
      
      Detta fungerar för följande datatyper:
      
      
      
      1. Binary och varbinary. Ersättningsvärdet är 0x4D4FFB23A49411D5BDDB00A0C906B7B4 (jag använde newid() för att skapa det värdet).
      
      
      2. Numeric och decimal. Ersättningsvärdet är 71428763405345098745098.8723, ett slumpmässigt genererat värde med 28 siffror som är max för SQL Server 7.0 som startats utan det speciella alternativet /p.
      
      
      3. Real och float. Ersättningsvärdet är 8764589764.22708E237. Skulle vi använda räkneord som värde så skulle jämförelseprocessen bli upp till 30% långsammare.
      
      
      Exempel på detta:
      
      
CASE WHEN d1.[NumericCol] IS NULL THEN 71428763405345098745098.8723 ELSE d1.[NumericCol] END =
CASE WHEN d2.[NumericCol] IS NULL THEN 71428763405345098745098.8723 ELSE d2.[NumericCol] END

CASE WHEN d1.[BinaryCol] IS NULL THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[BinaryCol] END =
CASE WHEN d2.[BinaryCol] IS NULL THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[BinaryCol] END

   
   
   4. Använda CASE med ett ersättningsvärde (om det är null) med en likvärdig datatyp, men med ett värde som till och med i teorin aldrig skulle finnas i datan. Detta gäller följande datatyper:
      
      
      
      1. Tinyint, smallint och bit. Ersättningsvärdet är 99,999. Maxvärdet för tinyint är 255, för smallint är det 32,767 och för bit är det 1. Även i detta fall, som med real/float typerna, så kan vi använda räkneord (för att göra scriptet enhetligt och därmed enklare), men då skulle jämförelsen ta mellan 30-40% längre tid.
         
         Exempel:
         
         
CASE WHEN d1.[TinyintCol] IS NULL THEN 99999 ELSE d1.[TinyintCol] END =
CASE WHEN d2.[TinyintCol] IS NULL THEN 99999 ELSE d2.[TinyintCol] END

      
      
      2. Bigint (datatyp i SQL Server 2000), money och smallmoney. Ersättningsvärdet är räkneordet 971428763405345098745.8723. Det är större än maxvärdet för money (2^63-1).
         
         Exempel:
         
         
CASE WHEN d1.[MoneyCol] IS NULL
THEN 971428763405345098745.8723 ELSE d1.[MoneyCol] END =

CASE WHEN d2.[MoneyCol] IS NULL
THEN 971428763405345098745.8723 ELSE d2.[MoneyCol] END

   
   
   
   5. Använd CASE med ett ersättningsvärde (om det är null) vid icke-jämförbara datatyper, så att null-datan ej behöver konverteras.
      
      Detta gäller för datatyperna datetime och smalldate. Ersättningsvärdet är åter igen det beryktade '!#null$'. Men det finns en sak vi lätt skulle kunna missa: jämförelsemetoden. Den måste vara 9 eller 109, annars så förlorar vi sekunder och millisekunder och 'Aug 31, 2001 12:32:40:001' skulle tolkas vara likadant som 'Aug 31, 2001 12:32:02:238', vilket inte alls är fallet.
      
      Exempel:
      
CASE WHEN d1.[DatetimeCol] IS NULL THEN '!#null$' ELSE CONVERT(char(36),d1.[DatetimeCol],109) END =
CASE WHEN d2.[DatetimeCol] IS NULL THEN '!#null$' ELSE CONVERT(char(36),d2.[DatetimeCol],109) END

      
   
   
   
   6. 
      Använda CASE med ett ersättningsvärde (om det är null) för en ej-jämförelsebar datatyp som kan konverteras till subjektets datatyp.
      
      Detta kan användas för en kolumn med datatypen uniqueidentifier. Ersättningsvärdet är ett värde skapat med newid() som sedan kan konverteras till datatypen uniqueidentifier. Det vore enklare om en villkorslös konvertering gjorde datatyperna string och uniqueidentifier kompatibla med varandra. Det fungerar i SQL Server 7, men SQL Server 2000 ger dig då meddelandet "Syntax error converting from a character string to uniqueidentifier." Därför har vi inget val, utan måste använda explicit konvertering.
      
      Exempel:
      
      
CASE WHEN d1.[IdCol] is null
THEN CONVERT(uniqueidentifier,'1CD827A0-744A-4866-8401-B9902CF2D4FB')
ELSE d1.[IdCol] END =
CASE WHEN d2.[IdCol] is null
THEN CONVERT(uniqueidentifier,'1CD827A0-744A-4866-8401-B9902CF2D4FB')
ELSE d2.[IdCol] END

   
   
   7. Datatyperna text, ntext och image kan ej användas för att jämföras direkt på samma sätt som andra fält. Men som sagt så kan man göra det möjligt att jämföra ändå genom att anropa funktioner i samband med jämförandet.
   
   
   
   8. Beteendet hos datatypen variant är ganska specifikt då du jämför data med olika datatyper. Men, när vi använder min lagrade procedur så ska vi inte inbilla oss att vi kan jämföra olika datatyper för att bestämma vilket som är störst (och fundersamt rynka pannan då vi ser att 1 är större än '9999'), vi behöver bara få ut ett av två möjliga svar: Lika eller Olika. Vi är angelägna om:
      
      
      
      1. att inte få "Lika" när det faktiskt finns skillnader i datan.
      
      
      2. att inte få "Olika" när datan faktiskt är lika
      
      
      
      Det första misstaget skulle kunna vara om en SQL_variant konverteras emedan den pågående jämförelsenprocessen. Men datatypsfamiljen SQL_variant är alltid noggrannt rankade vad det gäller jämförelser, och detta resulterar t.ex. i att binary 0x61 aldrig tolkas som likadant med char(1) 'A'. Detta är ju helt perfekt!
      
      Sedan kan vi komma fram till att:
      
      
CREATE TABLE ChkSQLv (CharSQLvCol SQL_variant, BinarySQLvCol SQL_variant)
INSERT INTO ChkSQLv VALUES (CONVERT(char(1),'A'),CONVERT(Binary(1),0x61))
SELECT * FROM ChkSQLv WHERE ISNULL(CharSQLvCol,'!#null$')=ISNULL(BinarySQLvCol,'!#null$')

      inte returnerar någonting, precis som
      
      
SELECT * FROM ChkSQLv WHERE BinarySQLvCol=CharSQLvCol

      medan däremot
      
      
CREATE TABLE ChkBinChar (CharCol char(1), BinaryCol binary(1))
INSERT INTO ChkBinChar VALUES ('A',0x61)
SELECT * FROM ChkBinChar WHERE BinaryCol=CharCol

      returnerar raden!!
      
      En SQL_Varaint null har aldrig en grundläggande datatyp, om en sådan är subjektivet i funktionen ISNULL, så uppstår aldrig felmeddelandet "Converting error":
      
      
DECLARE @SQLvCol SQL_Variant
SET @SQLvCol = CONVERT(int,null)
SELECT ISNULL(@SQLvCol,'!#null$')

      Emedan denna kod skapar just ett sådant felmeddelande:
      
      
DECLARE @IntCol int

SET @IntCol = null
SELECT ISNULL(@IntCol,'!#null$')

   
   
   
   9. Nu till vår sista paragraf i vår (förvisso ej kompletta) "Hoppsan!"-lista. En primär nyckel kan aldrig innehålla en kolumn som tillåter null-värden. Men det kan däremot unika restriktioner och unika indexeringar göra. Det är mycket sällsynt, men ändå en möjlighet vi måste räkna med. Därför måste man bygga tunga konstruktioner för att jämföra nycklarna i tabellerna också.
      
   
   

Hur man använder den lagrade proceduren sp_CompareDB

För att gör en lång historia kort, så var det en ganska stor utmaning att göra ett script som inte bara jämför databaser, utan som dessutom är snabbt, inte har några orimliga begränsningar och är lätt att använda. Allt du behöver göra för att installera sp_CompareDB är att köra scriptet mot Master databasen. Sedan ställer du in inställningen "Results in Text" i Query Analyzer för att få resultatet och kommentarerna på samma sida.

När den lagrade proceduren väl är installerad så är det bara att köra:


sp_CompareDb '[databas1]', '[databas2]'

och sedan utan större möda luta dig tillbaka för att skåda skillnaderna mellan databaserna.

Om du jämför med en databas från en länkad server så skulle du skriva:


sp_CompareDB ‘[LänkadServer1].[databas1]’,’[databas2]’

Du kan dessutom ange följande parametrar till den lagrade proceduren, förutom de självklara db1 (parameternamn @db1) och db2 (parameternamn @db2).

@tablist - kommaseparerad lista med de tabeller du vill jämföra. Om denna lämnas tom så jämförs alla tabeller.

@NumbToShow - Antalet rader med skiljaktigheter att visa. Standardvärdet är 10. Idka försiktighet när du ökar det. För tabeller med en unik nyckel så skulle bara nycklarna på rader som innehåller skiljaktigheter visas. Men för tabeller utan en unik nyckel, så visas all data för varje rad. Det är trots allt osannolikt att någon behöver en oändlig rapport fylld av siffror och bokstäver att gå vilse i. Om databaserna är helt olika så finns det ju ingen mening med att jämföra dem.

@OnlyStructure - om du sätter denna till 1 så jämförs ingen data, bara strukturerna. Standardvärdet är 0.

@NoTimestamp - om du sätter denna till 1 så jämförs inte kolumner med datatypen TimeStamp. Standardvärdet är 0.

@VerboseLevel - om du sätter denna till 1 så ser du de SQL-strängar som användes för att jämföra datan. Dessa kan lätt användas för att synkronisera datan i databaserna om du skulle behöva göra det.

Nu har jag bara en sak kvar att säga: Denna lagrade procedure är helt säker. Den hämtar endast data från databaserna, och utför inga ändringar alls. Databaserna kan till och med vara skrivskyddade. Men om du har stora tabeller utan unika nycklar så måste du se till att ha tillräckligt med utrymme på hårddisken innan du kör den lagrade proceduren. Det fria utrymmet måste vara minst dubbelt så stort som det utrymme som tas upp av den sörsta tabellen i databasen.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: