Compare SQL Server Databases with sp_CompareDB


C. Use CASE with a replacement value (if null) of a comparable data type, but a value that practically will never appear in the data. We can’t use ISNULL, because the actual description of a column can have short length (for example, binary can be binary(1), so a maximum for the replacement is just 0xff).

It’s for:

C.1 Binary, varbinary data types. The replacement value is 0x4D4FFB23A49411D5BDDB00A0C906B7B4 (I used newid() to generate the replacement value)

C.2 Numeric, decimal. The replacement value is 71428763405345098745098.8723, a random value with a precision of 28 digits, the maximum for SQL Server 7.0 started without the special option /p.

C.3 Real, float. The replacement value is 8764589764.22708E237. Using numeric data as the replacement will make the comparison about 30% slower.

Examples of this predicate: 

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 


D. Use CASE with a replacement value (if null) of a comparable data type, but a value that even theoretically will never appear in the data. It’s for:

D.1 tinyint, smallint, bit. The replacement value is 99,999. The maximum for tinyint is 255, for smallint is 32,767, for bit is 1. Again, as in the case with real/float data we can use numeric data (to make the script code unified and therefore simpler), but it will make the comparison 30-40% slower.

Example of this predicate: 

CASE WHEN d1.<TinyintCol> IS NULL THEN 99999 ELSE d1.<TinyintCol> END = 
CASE WHEN d2.<TinyintCol> IS NULL THEN 99999 ELSE d2.<TinyintCol> END
 

D.2 for bigint (SQL Server 2000 data type), money, smallmoney. The replacement value is numeric 971428763405345098745.8723. It’s bigger than maximum for money (2^63-1).

Example of this predicate: 

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


E. Use CASE with a replacement value (if null) of a not-comparable data type, so that not-null data have to be converted.

It’s for datetime, smalldatetime data types. The replacement value is again the notorious ‘!#null$’. There is one thing we can easily miss: the style of conversion. It must be 9 or 109, otherwise we’ll lose seconds and milliseconds and ‘Aug 31, 2001 12:32:50:011′ will be equal to ‘Aug 31, 2001 12:32:02:238′, which is not true.

Examples of this predicate: 

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


F. Use CASE with a replacement value (if null) of a not-comparable data type that can be converted to the subject data type.

It can be used for a uniqueidentifier. The replacement value is a string formed from newid() value that can be converted to uniqueidentifier data type. It would be easier if an implicit conversion made the string and uniqueidentifier data types compatible. This does work in SQL Server 7, but SQL Server 2000 returns the error message “Syntax error converting from a character string to uniqueidentifier.” We therefore don’t have a choice but to use explicit conversion.

Example of this predicate:

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


G. Text, ntext and image datatypes cannot be used in a predicate. As was said before, applying functions against these data types can make it possible.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

One Response to “Compare SQL Server Databases with sp_CompareDB”

  1. So how do I create a @TabList to limit the comparison to the specified tables? I tried ‘table1, table2′, ‘dbo.table1,dbo.table2′. Nothing worked. The message I get is “Msg 8114, Level 16, State 1, Procedure sp_CompareDB, Line 0
    Error converting data type varchar to bit.”

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |