To check on NULL or 0

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

I want to h开发者_高级运维ave 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.

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)

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.

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.

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.

EXISTS could be faster than COUNT, especially if the rows you are looking for is very large, and besides you should not dwell too much on micro-optimizations. Strive for code readability first, so others reading your code can easily glean the intent of your query. Anyway, COUNT will still attempt to loop the rows even it already find the value you are looking for. EXISTS is a directive for your RDBMS to stop searching as soon as it matches your criteria.

And besides, the logic of your code is if something exists, that's why there's a first-class language construct introduced to the language to facilitate that. And of course database vendor's engineers will put effort to optimize that, given that it is basically a solved problem. You will not expect them to re-use their COUNT engine for EXISTS functionality, it would be pretty bad if EXISTS waited for the COUNT and its result, and then check if that result is greater than zero to make EXISTS return true. That won't happen.

EXISTS is pretty optimized

This is better, both in readability and performance:

IF EXISTS(SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] 
     WHERE VALUE = @myVal) 

And the following can result to many rows. If by any chance many rows in your table satisfy the filter, this will result to error:

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

The only redeeming factor of that query approach is if the VALUE field on your table is a primary key(but let's say you forgot to put primary key on that table), and it return two rows, that would not do a silent error, the RDBMS shall balk with error, and you can fix the error earlier compared to COUNT and EXISTS approach

select (case when id is null or id=0
then (dothis) else (dothis) end) as idState 
from [SomeTable].[dbo].[BlockedSubscriberNumbers]