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.