sql - Efficient way to check for inequality -

i'm writing trigger doing

if (@a <> @b)     ... 

but not work null values on either @a or @b. way it's done

if (@a <> @b) or (@a not null , @b null) or (@a null , @b not null) 

but involves 9 comparisons versus 1!

i

set ansi_nulls off 

but apparently not recommended (and deprecated).

so best solution this? take 9 comparisons simple inequality check when should 1? trigger not performance critical, need fast. when batch loading, slow down considerably.

peformance tests

here results of performance test checks inequality million times such 90% of time values not equal, 10% of time each value may null.

if (@a <> @b) or (@a null , @b not null) or (@a not null , @b null) 

result: average 3848ms

if (isnull(@a, 0) <> isnull(@b, 0))  

result: average 3942ms

if (@a = @b) goto equal else if @a null , @b null goto equal 

result: average 4140ms

if exists (select @a except select @b) 

result: average 7795ms

the times don't matter, it's relative difference counts. clearly, classic approach fastest. mssql has internally optimised type of check.

test run on macbook pro (intel core 2 duo, 2.4ghz, 8gb ram inside vista vm running mssql 2008 express).

ansi sql has is distinct from not implemented in sql server. can simulated in way doesn't rely on magic constants , sargable if used on columns

if exists (select @a except select @b)      print 'different'