Use 'ISNULL(x, 0) = 0' instead of (x is null OR x = 0)

What is the best o开发者_JAVA百科ption to use in sql?

ISNULL(x, 0)  = 0

or

(x is null OR x = 0)

Thanks

Unless you are experiencing any performance issues I would suggest using which ever is easiest for you to read. COALESCE and ISNULL are identical when there are just two values (i.e. NULL and 0)

The "best" one to use depends on the query you are writing, this is from the COALESCE page in BOL

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

So without knowing more about the whole query they are probably equivalent but YMMV and without actually testing you cannot second guess which will be faster.

Also, the comparison of ISNULL to COALESCE is only useful when you have to choose between two values. As soon as there are more than two you will need to use COALESCE.

In this situation, my pattern is to use coalesce:

SELECT @SomeVariable = COALESCE(@SomeInputVariable, 0)

OR

SELECT * FROM Table WHERE Column > COALESCE(@SomeInputVariable, 0)

They should be identical, but I actually did some tests on some very big select statements and isnull(x,0)=0 similar kind of condition proved to be faster.

The logical conclusion out of this is that the SQL team optimized this somehow.

Not to the point, coalesce should be used over isnull as coalesce is the standard sql method while isnull is MS specific. That means that in the future (depending on MS thoughts) isnull might be deprecated.