Microsoft SQL: CASE WHEN vs ISNULL/NULLIF

Question

Besides readability is there any significant benifit to using a CASE WHEN statement vs ISNULL/NULLIF when guarding against a divide by 0 error in SQL?

CASE WHEN (BeginningQuantity + BAdjustedQuantity)=0 THEN 0 
ELSE EndingQuantity/(BeginningQuantity + BAdjustedQuantity) END

vs

ISNULL((EndingQuantity)/NULLIF(BeginningQuantity + BAdjustedQuantity,0),0)

Answer 1

CASE WHEN (coalesce(BeginningQuantity,0) + coalesce(BAdjustedQuantity,0))=0 THEN 0 ELSE coalesce(EndingQuantity,0)/(coalesce(BeginningQuantity,0) + coalesce(BAdjustedQuantity,0)) END

your best option imho

Answer 2

I would use the ISNULL, but try to format it so it shows the meaning better:

SELECT
    x.zzz
        ,x.yyyy
        ,ISNULL(
                   EndingQuantity / NULLIF(BeginningQuantity+BAdjustedQuantity,0)
                ,0)
        ,x.aaa
    FROM xxxx...

Answer 3

In your example I think the performance is negligible. But in other cases, depending on the complexity of your divisor, the answer is 'it depends'.

Here is an interesting blog on the topic:

For readability, I like the Case/When.

Answer 4

Sorry, here is the little more simplify upbuilded sql query.

SELECT 

(ISNULL([k1],0) + ISNULL([k2],0)) /

CASE WHEN (
(
   CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
   CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
) > 0 )
THEN
(
  CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
  CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
)
ELSE 1 END

FROM dbo.[Table]

Answer 5

In my opinion, using Isnull/Nullif is faster than using Case When. I rather the isnull/nullif.

Answer 6

Remember that NULL is different from 0. So the two code snippets in the question can return different results for the same input.

For example, if BeginningQuantity is NULL, the first expression evaluates to NULL:

CASE WHEN (NULL + ?)=0 THEN 0 ELSE ?/(NULL + ?) END

Now (NULL + ?) equals NULL, and NULL=0 is false, so the ELSE clause is evaluated, giving ?/(NULL+?), which results in NULL. However, the second expression becomes:

ISNULL((?)/NULLIF(NULL + ?,0),0)

Here NULL+? becomes NULL, and because NULL is not equal to 0, the NULLIF returns the first expression, which is NULL. The outer ISNULL catches this and returns 0.

So, make up your mind: are you guarding against divison by zero, or divison by NULL? ;-)