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).

Community · Accepted Answer · 2018-11-13 23:30:48Z

7

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'

edited Nov 13, 2018 at 23:30

CommunityBot

11 silver badge

answered Mar 18, 2013 at 7:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Karl Over a year ago

very nice - I assume this avoids the possibility of a collision; e.g. defaulting the value to a valid data value or of 2 NULLs being defaulted to the same value.

Martin Smith Over a year ago

@Karl - Yes it doesn't rely on there being some sentinel value that can't occur in the data.

Martin Smith Over a year ago

@HermanSchoenfeld - Unlike the other two answers it is correct for all possible variable values. It will be plenty fast enough. This comparison is something pointless spending any time optimising. It will never be a significant factor.

Herman Schoenfeld Over a year ago

@MartinSmith: even if you have that statement run 20 times per record per update per table?

Martin Smith Over a year ago

@HermanSchoenfeld - Instead of guessing how it may be implemented internally you should focus on optimizing proven bottlenecks. This won't be.

|

Harsh Baid · Accepted Answer · 2013-03-18 07:42:28Z

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

answered Mar 18, 2013 at 7:42

Harsh Baid

7,2496 gold badges51 silver badges94 bronze badges

7 Comments

Martin Smith Over a year ago

What if 0 is a legitimate value? If @A is 0 and @B is NULL it will report they are the same.

alzaimar Over a year ago

Maybe by a fraction. But it is more readable and that's what it's all about.

Harsh Baid Over a year ago

I have supposed that @A and @B are Int datatype so I have put default value 0

Herman Schoenfeld Over a year ago

Readability is nice, in this situation, performance is more important. I'm checking for inequality average 20 times per record. Most of the time it's not going to match so every "check" results in 9 comparisons. That's already 180 comparisons per row. This trigger runs on every update for all tables. The DB is batch loaded on occassion. Performance matters.

Karl Over a year ago

In addition to 0 being a valid number, what is both values are NULL, (which should still not match but will in this example)

|

Tony Shih · Accepted Answer · 2013-03-18 07:43:10Z

2

you can do following

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

answered Mar 18, 2013 at 7:43

Tony Shih

4365 silver badges6 bronze badges

Comments

Herman Schoenfeld · Accepted Answer · 2013-03-19 01:25:54Z

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).

Herman Schoenfeld · Accepted Answer · 2013-03-18 09:19:07Z

I know many won't like this, but as performance is an important issue I'm using this (unless someone shows 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 as almost always @A and @B are equal resulting in only 1 comparison. In the minority of cases when @A and @B are not equal, an average of 1.5 further comparisons are required to determine non-equality.

This is for column-level auditing of row updates within a trigger. The comparisons @A and @B denote "old value" and "new value" for every column in every row in every update for every table.

Collectives™ on Stack Overflow

Efficient way to check for inequality

5 Answers 5

8 Comments

7 Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

7 Comments

Comments

Comments

1 Comment

Related