Geek Answers Handbook

An effective way to check inequality

I am writing a trigger that does

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

but this will not work for NULL values ​​on @A or @B. This is usually done

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

but this implies up to 9 comparisons versus 1!

I could do

 SET ANSI_NULLS OFF

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

So what is the best solution for this? Just take 9 comparisons for a simple check of inequality, when should it be 1? A trigger is not performance critical, but it must be fast. When batch loading, this can significantly slow it down.

TIME TESTS

Below are the results of a performance test that checks inequality a million times, so that in 90% of cases the values ​​are not equal, 10% of the time each value can be zero.

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

Result: average 3848 ms

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

Result: average 3942 ms

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

Result: average 4140 ms

 IF EXISTS (SELECT @A EXCEPT SELECT @B)

Result: average 7795 ms

Times don't matter much, it's the relative difference that matters. Obviously, the classic approach is the fastest . MSSQL is probably internally optimized for this type of validation.

Testing on a MacBook Pro (Intel Core 2 Duo, 2.4Ghz, 8 GB of RAM in a virtual VM running on MSSQL 2008 Express).

+4
source share
5 answers

Below are the results of a performance test that checks inequality a million times, so that in 90% of cases the values ​​are not equal, 10% of the time each value can be zero.

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

Result: average 3848 ms

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

Result: average 3942 ms

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

Result: average 4140 ms

 IF EXISTS (SELECT @A EXCEPT SELECT @B)

Result: average 7795 ms

Times don't matter much, it's the relative difference that matters. Obviously, the classic approach is the fastest . MSSQL is probably internally optimized for this type of validation.

Testing on a MacBook Pro (Intel Core 2 Duo, 2.4Ghz, 8 GB of RAM in a virtual VM running on MSSQL 2008 Express).

+1
source

ANSI SQL has IS DISTINCT FROM , but it is not implemented in SQL Server. It can be simulated so that it does not rely on magic constants and can be comparable when used in columns

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

In my opinion, this is the best way to check the nullability value and provide a 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, replace_value is returned after the implicit conversion to type check_expression if the types are different. replace_value can be truncated if the value of replace_value is greater than check_expression.

Contact: MSDN - ISNULL

+2
source

you can do the following

ISNULL(@A,'N') <> ISNULL(@B,'N')

+2
source

I know that many will not like this, but since performance is an important important issue, I use this (unless someone has a good reason)

 IF @A = @B GOTO Equal ELSE IF @A IS NULL AND @B IS NULL GOTO Equal NotEqual: PRINT 'Different' Equal:

This is much faster since @A and @B are almost always equal, which leads to only 1 comparison. In fewer cases where @A and @B are not equal, an average of 1.5 additional comparisons are required to determine the disequilibrium.

This is to check the level of row update columns in a trigger. Comparisons @A and @B denote the "old value" and "new value" for each column in each row of each update for each table.

0
source

More articles:


All Articles