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

GO

–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

SET ARITHABORT OFF
SET ARITHIGNORE OFF
SET ANSI_WARHINGS OFF
go
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)

Output: 

Arithmetic overflow occurred.
Division by zero occurred.

(2 row(s) affected)

Conclusion

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

Advertisements

Understanding Division & Division by Zero

This is a quick review, which will lead up to addressing Division by Zero errors in SQL Server.

dividend ÷ divisor = quotient

Division is a way of splitting objects into equal groups.

Example 1)

12/3 = ? Can read analogous to:
Distribute 12 items into trays that can hold up to 3 items per tray.Empty Tray that can hold 3 items
How many trays will be completely filledTray filled completely with 3 items ?   = 4 full trays

Example 2)

12/12 = 1    Distribute 12 items into trays that can hold 12 items at a time.

12/6 = 2

12/4 =3

12/3 = 4

12/1 = 12

12/0 = ? looking at the pattern as the divisor decreases the quotient increases. This would imply a value greater than 12  but we only have 12 items to begin with for distribution.

This also implies distributing 12 items into trays that has no place holder to hold any items , the tray is completely flat.

So does this mean we can simply express it as a Remainder value ?

This can not even be expressed in the form of a remainder. Remainder are expressed in relation to the divisor as shown in the following example:

14/3 = 4 full trays +    = 4 + 2/3  which is mathematically equivalent to 12/3 + 2/3 = (12+2)/3 = 14/3

So the revised  formula is:

Dividend ÷ Divisor = Quotient + Remainder/Divisor      (where Divisor <> 0 )

Conclusion: 

Division by Zero is like a “Black Hole”, it is accounted for in all calculators and will generate an error.

I will show you how to handle Division By Zero T-SQL Queries in the next blog post.