This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 27%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPR%3C/text%3E%3C/svg%3E)
Im using SQL SERVER 2016 and it is not throwing dived-by-zero error. When I researched, I came to know that Starting with SQL Server 2012 (11.x), when a divide-by-zero error occurs during query execution, the database engine automatically converts the result of the expression to null and this behavior is referred to as a "silent truncation". Is this true ? Can you share some more information on this?
Sorry its referred as "silent nullification."
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Do you have an example reproducing behavior? I just tried select 1/0 and got the error as expected
SELECT 1/0 will give error all the time. In my case the scenario is:
Eh, no, by default you get an error for division by zero in SQL 2016, look below:
It is possible to change the rules, though:
But the setting ANSI_WARNINGS OFF is a pure legacy setting that you should not use. (ARITHABORT is OFF by default in most context on the other hand. But it is on by default in SSMS and Azure Data Studio. Both settings must be off to prevent arithmetic errors to raise exceptions)
SELECT 1/0 will give error all the time. In my case the scenario is:
Hi @Pavan Naik,
Could you please share the result from your side with the screenshot?
In addition, I searched about silent nullification online and do not find useful information.
From my side, it will get an error for division by zero.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.
In my case the scenario is:
ChatGpt gave me this info: PFA
In my case the scenario is:
ChatGPT is flat wrong! Why use AI, when you can use NS (Natural Stupidity)
I have a SP with rule/logic which does the calculations. when i run the SP sometimes it throws divide by zero some times it doesn't.
Since we don't see the code, it will have to be a bit of speculation. But I will assume that there are conditions that filters out the three problematic rows, so that they are not part of the result being returned.
In this case, ideally, there should not be any exception at all, since logically there is no error. However, depending on the query plan SQL Server may filter first and then compute the division, or it may divide before filtering. When composing the query plan, the optimizer does not consider that an operation can fail and take precautions accordingly. So therefore the query can fail even if the bad rows are logically filtered out.
A common way to avoid this is to write divisions like val1/nullif(val2, 0), so that you get a NULL for the division.
Hi @Pavan Naik,
I don't think Chatgpt is an enough perfect tool, don't believe everything it tells you.
If you’d like to handle division by zero gracefully, you can use the NULLIF function. NULLIF takes two arguments: the expression of interest, and the value you want to override. If the first argument is equal to the second, then NULLIF returns NULL; otherwise, it returns the first argument.
Regards，
Zoe Hui
If the answer is helpful, please click "Accept Answer" and upvote it.