Methods to avoid the SQL divide by zero error
Methods to avoid the SQL divide by zero error
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolMethods to avoid the SQL divide by zero error
October 21, 2019 by Rajendra Gupta This article explores the SQL divide by zero error and various methods for eliminating this.Introduction
We all know that in math, it is not possible to divide a number by zero. It leads to infinity: Source: www.1dividedby0.com If you try to do in calculator also, you get the error message – Cannot Divide by zero: We perform data calculations in SQL Server for various considerations. Suppose we perform an arithmetic division operator for calculating a ratio of products in a store. Usually, the division works fine, and we get the ratio: 12345 DECLARE @Product1 INT; DECLARE @Product2 INT; SET @Product1 = 50; SET @Product2 = 10; SELECT @Product1 / @Product2 ProductRatio; Someday, the product2 quantity goes out of stock and that means we do not have any quantity for product2. Let’s see how the SQL Server query behaves in this case: 12345 DECLARE @Product1 INT; DECLARE @Product2 INT; SET @Product1 = 50; SET @Product2 = 0; SELECT @Product1 / @Product2 ProductRatio; We get SQL divide by zero error messages (message id 8134, level 16): We do not want our code to fail due to these errors. It is a best practice to write code in such a way that it does not give divide by zero message. It should have a mechanism to deal proactively with such conditions. SQL Server provides multiple methods for avoiding this error message. Let’s explore it in the next section.Method 1 SQL NULLIF Function
We use NULLIF function to avoid divide by zero error message. The syntax of NULLIF function: 1 NULLIF(expression1, expression2) It accepts two arguments. If both the arguments are equal, it returns a null value For example, let’s say both arguments value is 10: 1 SELECT NULLIF(10, 10) result; In the screenshot, we can see that the output is null: If both the arguments are not equal, it returns the value of the first argument In this example, both argument values differ. It returns the output as value of first argument 10: 1 SELECT NULLIF(10, 5) result; Let’s modify our initial query using the SQL NULLIF statement. We place the following logic using NULLIF function for eliminating SQL divide by zero error: Use NULLIF function in the denominator with second argument value zero If the value of the first argument is also, zero, this function returns a null value. In SQL Server, if we divide a number with null, the output is null as well If the value of the first argument is not zero, it returns the first argument value and division takes place as standard values 12345 DECLARE @Product1 INT; DECLARE @Product2 INT; SET @Product1 = 50; SET @Product2 = 0; SELECT @Product1 / NULLIF(@Product2,0) ProductRatio; Execute this modified query. We can see the output NULL because denominator contains value zero. Do we want null value in the output? Is there any method to avoid null and display a definite value? Yes, we can use SQL ISNULL function to avoid null values in the output. This function replaces the null value in the expression1 and returns expression2 value as output. Let’s explore the following query with a combination of SQL NULLIF and SQL ISNULL function: First argument ((@Product1 / NULLIF(@Product2,0)) returns null We use the ISNULL function and specify the second argument value zero. As we have the first argument null, the output of overall query is zero (second argument value) 12345 DECLARE @Product1 INT; DECLARE @Product2 INT; SET @Product1 = 50; SET @Product2 = 0; SELECT ISNULL(@Product1 / NULLIF(@Product2,0),0) ProductRatio;Method 2 Using CASE statement to avoid divide by zero error
We can use a CASE statement in SQL to return values based on specific conditions. Look at the following query. It does the following task with the Case statement. The Case statement checks for the value of @Product2 parameter: If the @Product2 value is zero, it returns null If the above condition is not satisfied, it does the arithmetic operation (@Product1/@Product2) and returns the outputMethod 3 SET ARITHABORT OFF
We can use set methods to control query behavior. By default, SQL Server has a default value of SET ARITHABORT is ON. We get SQL divide by zero error in the output using the default behavior. The T-SQL syntax for controlling the ARITHABORT option is shown below: 1 SET ARITHABORT { ON OFF } Using ARITHABORT ON, the query will terminate with divide by zero message. It is the default behavior. For this demo, let’s enable it using the SET ARITHABORT ON statement: 1234567 SET ARITHABORT ON -- Default SET ANSI_WARNINGS ON DECLARE @Product1 INT; DECLARE @Product2 INT; SET @Product1 = 50; SET @Product2 = 0; SELECT @Product1 / @Product2 ProductRatio; We get the SQL divide by zero error messages: Using ARITHABORT OFF, the batch will terminate and returns a null value. We need to use ARITHABORT in combination with SET ANSI_WARNINGS OFF to avoid the error message: We can use the following query to check the current setting for the ARITHABORT parameter: 123 DECLARE @ARITHABORT VARCHAR(3) = 'OFF'; IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON'; SELECT @ARITHABORT AS ARITHABORT; The default ARITHABORT setting for SSMS is ON. We can view it using SSMS Tools properties. Navigate to Tools -> Options -> Advanced: Many client applications or drivers provide a default value of ARITHABORT is OFF. The different values might force SQL Server to produces a different execution plan, and it might create performance issues. You should also match the setting similar to a client application while troubleshooting the performance issues. Note: You should not modify the value of ARITHABORT unless required. It might create performance issues, as well. I would suggest using alternative methods (as described earlier) for avoiding SQL divide by zero error.Conclusion
In this article, we explored the various methods for avoiding SQL divide by zero error. It is best practice to be proactive and use these mechanisms so that code does not fail in real-time. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022