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 COUNT(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.

Solution 1

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)

Solution 2

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.

Solution 3

For anyone who want's it within the Query, you can do something like:

SELECT ISNULL(NULLIF(primaryValue,0),secondaryValue) as Value FROM SomeTable

Here the NULLIF will return primaryValue as NULL only if it is already NULL or if it is 0. The ISNULL will return secondaryValue if primaryValue is NULL.

Thus, if primaryValue is NULL or 0, then it will return secondaryValue.

Solution 4

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.

View more solutions

Comments

[Joe Stefanelli]

Look more carefully at the parens in the original query. The OP is not trying to test @myVal>0, he's trying to test the result of the query, ID.

[msarchet]

@Joe Stefanelli, nice catch fixing

[Incognito]

Actually I do not care on comparing is NULL grater than 0. If the result of query will be NULL and it will be compared with 0 it will go to ELSE block, which means that there is no value in table equal to @myVal. So to summarize, I just need to check is the value already there or not.

[msarchet]

So you are trying to get the number of rows returned by the query then?

[Incognito]

@msarchet in general I need to check is there any record or not. Not important how many rows are there. Anyway it can be on or none as VALUE field is unique.

[Incognito]

@msarchet I have mentioned in question "I want to have the fastest way to check is value already in table". But seems I wrote it wrongly :)