To check on NULL or 0

Question

What is faster in SQL to check value for NULL or 0

I want to have the fastest way to check is value already in table.

For example which is faster :

IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] 
     WHERE VALUE = @myVal) is null )
BEGIN
....
END
ELSE
BEGIN
....
END

or

IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
     WHERE VALUE = @myVal) > 0 )
BEGIN
....
END
ELSE
BEGIN
....
END

Also does in T-SQL plays role where the frequent accruing case must be. I mean is it will be faster that in most cases it will fail into IF block and slower if it will mostly go into ELSE.

Answer 1

Checking for NULL is much faster than checking for 0, but I think that, for those queries, we're talking about different things: they will produce different results.

Answer 2

Well these will actually do different things, you can't check if a NULL is greater than 0 in SQL.

What you should do is this.

    IF (ISNULL((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] 
         WHERE VALUE = @myVal), 0) > 0 )
    BEGIN
    ....
    END
    ELSE
    BEGIN
    ....
    END

And did you actually mean equals 0? Because your question states

What is faster in SQL to check value for NULL or 0

This part is in regards to Joe's comment about multiple result sets

You could do some kind of aggreate function over the Select ID using the ISNULL to determine whether or not any of the values are greater than 0 (assuming of course that all of your values are greater than 0).

In regards to figuring out if any rows contained that information per the OPs comment

IF (Select Count(ID) from [SomeTable].[dbo].[BlockedSubscriberNumbers] Where Value = @myVal) = 0 Shoud tell you if there are any Rows containing that value

Final Edit

Just use Exists

If Exists(Select ID From [SomeTable].[dbo].BlockedSubscriberNumbers] Where Values = @myVal)

Answer 3

IMO, each record/row in table contains NULL BITMAP (2 bytes, each bit of which tells about null-ness of one/each of the column's value in this row), so before selecting/reading real stored value this process of reading data passes this checking/reading of the corresponding bit from this NULL bit map.

In case of NULL (or, in other words, "is not null" check), the reading process stops at this stage, while other selects/checks/comparison might (or might not, this depends) continue, so "is null check" cannot be slower. Even more, NULL values at the end of the row are not even stored, no storage is occupied by them. They are virtually and, sometimes, practically nothing.

Though, the problem is that your TSQL examples in question and question itself are ambiguous with possible multiple interpretation and answers.