T-SQL Handling Division by Zero in SELECT Statement

When performing division calcuations, you must always take appropriate measures to handle for division by zero otherwise SQL Server will throw an error.

USE AdventureWorks2012


–Query is aborted at the first occurence of Division By Zero Error

select OrderQty,StockedQty,ratio = OrderQty/StockedQty
from Purchasing.PurchaseOrderDetail

Error Output:

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

Solution 1: Use of CASE to check for   If divisor =zero then return zero else calculate dividend ÷ divisor

select OrderQty,StockedQty,ratio = case when StockedQty =0 then NULL else OrderQty/StockedQty end

from Purchasing.PurchaseOrderDetail

Solution 2: Using NULLIF function to return NULL if divisor=0.  performing any calculation on a null yields a null. So a number/NULL will result in a NULL.  You can then use the ISNULL() to return a zero.

select OrderQty,StockedQty
,ratio = OrderQty/nullif(StockedQty,0)
,ratio2 = isnull(OrderQty/nullif(StockedQty,0),0)
from Purchasing.PurchaseOrderDetail po

NULLIF (expression1, expression2) is equivalent to  CASE WHEN Expression1=Expression2 THEN NULL ELSE Exression1 END)

Things to Watch Out For: 

There is a performance implication if a subquery is used within NULLIF() function. By substituting the subquery to the equivalent CASE statement we can see that the subquery can be executed twice. As shown below

create table #t(id int null)
insert into #t values(1)
select nullif( ( select count(*) from #t ), 1)

— Improve Performance by moving the subquery outside, thus eliminating the second subquery call.

select nullif(t.c, 1)
from (select c= count(*) from #t)t

Solution 3: Using SET options

Quote From BOL

“If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views

Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.”

so with the following set

DBCC USEROPTIONS –Returns the SET options active (set) for the current connection.

–Division By Zero Error are reported but does not cause query to Abort/Fail. Query executes successfully because NULL were returned whenever a division by zero occured.
select OrderQty,StockedQty,ratio = OrderQty/StockedQty
from Purchasing.PurchaseOrderDetail

Things to Watch Out For: 

Running following INSERT WITH above set options,  you can see that not only are division by zero taken care of, but also Arithmetic overflows. If you are storing these calculations then you would want the insert to fail and not store a NULL value (if the column allows NULL). In a Overflow situation, the input might be an extremely big invalid value that does not make sense for your business, or it is a valid value and the table column datatype size needs to be increased.  Mainly due to this reason I would avoid using Solution 3.

create table #t(i tinyint null)
insert into #t
values (12/0),(150*2)


Arithmetic overflow occurred.
Division by zero occurred.

(2 row(s) affected)


I would recommend using either solution 1 or 2 to handle division by zero in SELECT statements.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s