# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « SQL Server 2008 Upgrade Technical Reference Guide Available For Download SQL Server Precision And Scale Problems »

# SQL Server efficient handling of divide by zero

by George Mastros (gmmastros) on Nov 25, 2008 in categories Data Modelling & Design

There are various methods to accommodate this problem, let's examine a few of them and also check performance. When you divide two numbers, and the denominator is 0, the result of the operation is undefined. In reality, we usually define some alternative number to use (usually 0). In the example and the code I show below, I will assume that the expected result for a 'divide by zero' condition is 0.

For most of us, the typical method for accommodating divide by zero is to first check the denominator to see if it is equal to zero. If it is, then return 0, otherwise perform the division. Like this…

tsql
1. Select Case When Denominator = 0
2.             Then 0
3.             Else Numerator/Denominator
4.             End As [CalculatedColumn]

Most programmers are very familiar with this syntax, and often times, it's the best way to approach the problem. However, there is another method that can be better under the right circumstances.

The problem occurs when the denominator is a calculated value, and that calculation is slow.

tsql
1. Select Case When dbo.SlowPerformingFunction() = 0
2.             Then 0
3.             Else Numerator / dbo.SlowPerformingFunction()
4.             End As [CalculatedColumn]

In this case, the SlowPerformingFunction is evaluated twice, once in the test and again in the division. There is a way to write this query so that the function is only evaluated once. In order to understand this method, we need to know about other functions.

SQL Server has a NULLIF function. This function accepts 2 parameters. If the first parameter is equal to the second parameter, NULL is returned. Otherwise, the value in the first parameter is returned. EX:

tsql
1. Select NullIf(0,0) -- Returns NULL
2. Select NullIf(7,0) -- Returns 7

Another handy function to know is COALESCE. Coalesce accepts multiple parameters. It checks each parameter and returns the first one that is NOT NULL. If all parameters are NULL, NULL is returned. Ex:

tsql
1. Select Coalesce(NULL, NULL, NULL, 'Apple') -- Returns Apple

Lastly, we need to think about the division. If you try to divide any number by zero, you will get an error. However, if you divide a number by NULL, you get NULL. We can use this to our advantage to prevent the 'divide by zero' error while evaluating slow performing functions just once. Like this:

tsql
1. Select Coalesce(Numerator / NullIf(dbo.SlowPerformingFunction(), 0), 0) As [CalculatedColumn]

In this case, NULLIF will return the value from the SlowPerformingFunction if that value is not 0. If it is, NULLIF will return NULL. We then perform the calculation, and if the result is NULL, we replace it with 0 using the Coalesce function.

Is this method actually faster?

The true answer is, 'It depends'. If your denominator is a scalar value (parameter to the procedure or a pre-calculated value), then NO. The Case/When method can be faster. You see, if the denominator is zero, the division does not occur. However, if you have a poorly performing function, then the Coalesce/NullIf method will be faster because the function is called only once. Sure, the division occurs even if the result of the function is 0, but it's NULL math, and a simple division too. My recommendation is to write the query both ways and compare the performance. Whichever method performs better is the one you should use.

6432 views

Comment from: Suraj Shrestha [Visitor] · http://techcreeze.blogspot.com

Hi,

I think its better to get the output of SlowPerformingFunction() in a
variable and use in case when statement.

After all, if it is matter about calling the function twice.
12/08/08 @ 10:07
Comment from: Gil Shayer [Visitor]
Short and efficient !
I loved it !!!
12/11/08 @ 02:33
Comment from: sunil [Visitor]
Beautiful!!!
04/22/09 @ 12:04
Comment from: sally attalla [Visitor]
Thanks alot
it very usuful
06/10/09 @ 03:22
Comment from: Jinx [Visitor]