Efficient way to check for inequality

Question

I'm writing a trigger that is doing

IF (@A <> @B)
    ...

but this will not work for NULL values on either @A or @B. The way it's normally done is

IF (@A <> @B) OR (@A IS NOT NULL AND @B IS NULL) OR (@A IS NULL AND @B IS NOT NULL)

but this involves up to 9 comparisons versus 1!

I could do

SET ANSI_NULLS OFF

but apparently this is not recommended (and to be deprecated).

So what is the best solution for this? Just take 9 comparisons for a simple inequality check when it should be 1? The trigger is not performance critical, but it does need to be fast. When batch loading, this could slow it down considerably.

PEFORMANCE TESTS

Here are the results of a performance test that checks for inequality a million times such that 90% of the time the values are not equal, 10% of the time each value may be null.

IF (@A <> @B) OR (@A IS NULL AND @B IS NOT NULL) OR (@A IS NOT NULL AND @B IS NULL)

Result: average 3848ms

IF (ISNULL(@A, 0) <> ISNULL(@B, 0)) 

Result: average 3942ms

IF (@A = @B) GOTO Equal ELSE IF @A IS NULL AND @B IS NULL GOTO Equal

Result: average 4140ms

IF EXISTS (SELECT @A EXCEPT SELECT @B)

Result: average 7795ms

The times don't really matter, it's the relative difference that counts. Clearly, the classic approach is the fastest. Likely MSSQL has internally optimised for this type of check.

Test run on MacBook Pro (Intel Core 2 Duo, 2.4Ghz, 8GB RAM inside a Vista VM running MSSQL 2008 Express).

Answer 1

ANSI SQL has IS DISTINCT FROM but this is not implemented in SQL Server. It can be simulated in a way that doesn't rely on magic constants and is sargable if used on columns

IF EXISTS (SELECT @A EXCEPT SELECT @B)
     PRINT 'Different'

Answer 2

In my opinion this is the best way to check for nullability and provide default value.

IF ( ISNULL(@A, 0) <> ISNULL(@B, 0) )

General Syntax

ISNULL ( check_expression , replacement_value )

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

Refer: MSDN - ISNULL