Is there any potential issue removing this ISNULL statement?

Question

Recently we uncovered a long running proc and one of my colleagues pointed out that that dropping the ISNULL in the where clause sped things up for reasons pointed out in this post Is there is any Performance issue while using ISNULL() in SQL Server?. I did some Google searching but couldn't find out for sure if there was any danger in missing data. I know if you say something like this

WHERE ID NOT IN (SELECT MemberID FROM Person WHERE MemberId IS NOT Null)

and MemberId is nullable you won't get any matches if you don't include the "IS NOT NULL" but I think that is a different issue? Maybe not?

Here is the change my coworker is proposing.

where ISNULL(ct.DisabilityBenefitsConnectAuthorized,0) = 1

Would become

where ct.DisabilityBenefitsConnectAuthorized = 1

It doesn't seem to be effecting results however I am not 100% sure that it couldn't and we just don't know it.

score 3 · Accepted Answer · answered Nov 08 '12 at 14:37

why not just try it out yourself with a simplified example, like:

declare @x bit

--@x is currently null
if isnull(@x,0)=1 begin print 'is one!' end else begin print 'not one!' end
if @x=1 begin print 'is one!' end else begin print 'not one!' end

set @x=0
if isnull(@x,0)=1 begin print 'is one!' end else begin print 'not one!' end
if @x=1 begin print 'is one!' end else begin print 'not one!' end

set @x=1
if isnull(@x,0)=1 begin print 'is one!' end else begin print 'not one!' end
if @x=1 begin print 'is one!' end else begin print 'not one!' end

OUTPUT:

not one!
not one!
not one!
not one!
is one!
is one!

based on the above, it looks like you can remove the ISNULL()

Because I wasn't clever enough to come up with this nifty test! Fortunately you where and it indeed proves that there is no issue. Thanks! — Kenn, Nov 08 '12 at 15:04

score 1 · Answer 2 · answered Nov 08 '12 at 14:22

1

That change will be fine.

Neither NULL or 0 equate to 1, therefore the results will be the same.

answered Nov 08 '12 at 14:22

Curtis

101,612
66
270
352

score 0 · Answer 3 · answered Nov 08 '12 at 14:22

MemberId is nullable you won't get any matches if you don't include the "IS NOT NULL" but I think that is a different issue? Maybe not?

No it is not. You have to test for nullable MemberID values in the WHERE clause of the inner query. You won't get any values if there is any NULL values MemberID in the Person table. May be it would be better if you use LEFT JOIN with WHERE leftids IS NULL instead of the IN predicate, it is safer.

Is there any potential issue removing this ISNULL statement?

3 Answers3