Working with Divide by Zero in SQL

One of the biggest annoyances when working with numbers in SQL Server is the possibility of dividing by zero.  Nothing can get a programmer more angry than seeing one of these errors in his/her programs:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Some solutions I have tried before include adding the smallest of values to your denominator, but this could throw your results off.  If you are working with thousandths of a penny, like I do, this could really mess up your reports.

select 1/(0+0.0000005)

Most of the time, a zero value is the most appropriate solution, so you shouldn't be adding anything to it.  I have found a pretty simple solution in SQL to this (what could be) complex problem.  Microsoft SQL Server has a fancy expression called NULLIF().

MSSQL NULLIF()

NULLIF works this way: specify the value or expression that may contain the zero and then specify the value you are looking for (in this case, zero).  When the value you are looking for is found, a NULL is returned.  From there, you are working with a NULL value instead of working with an error.

This returns a NULL:

select 1/NULLIF(0, 0)

You could then update your statement so the final result is a zero, if that is appropriate:

select ISNULL(1/NULLIF(0, 0), 0)

In this way, you can avoid getting a divide by zero error while actually showing the zero as a result.