Earlier I asked a puzzle SQL SERVER – Puzzle – Why Decimal is Rounded Up? and it was very well received by all of you. You can read various comments posted in the blog post and they are indeed very enlightening. After posting that blog post, I received quite a lots of emails asking for more puzzle similar to that. Based on your request, here is another puzzle which is very similar to the earlier puzzle but have a very different approach.
The question is why following T-SQL gives a famous divide by zero error.
SELECT 10000/(17/17/17) AS Result2;
When we execute above query it gives following error:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Here is the screenshot of the error.
Hint: Read the comments in this blog post.
I will be announcing the winner of this puzzle in my newsletter. There will be a surprise gift of USD 29. Leave your answers in the comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
Int / int = int
17/17 => 1
1/17 => 0
10000/0 => Divide by zero error encountered.
Thanks.
The denominator resolves to 0 since all numbers are integers and inside of the parenthesis the division is solved from left to right. 17/17 = 1 and then 1/17 = .0588 which is rounded to 0 to maintain the integer data type. Now the numerator is divided by 0 and throws an error
In this situation SQL will translate 17 as an int so it is rounding the result to 0 and then attempting to divide 10000 by 0. Since division is a distribution and we can’t distribute any number against 0. If you would like see SQL perform this math you can write “select 10000/(17.00/17.00/17.00) AS Result2” then we will get result in decimal or float however even this won’t be completely accurate because SQL will do some rounding during the calculation.
Hi Pinal,
Driving point for the result here is that all the numbers are integer hence the complier will perform all integer operations.
Now moving to the equation ,we have brackets in the denominator , so following BODMAS rule the compiler will first perform the denominator operation i.e. (17/17/17). Inside () if we start calculating from left to right we first have 17/17 which gives 1 (division is for integers), now the resultant equation is something like this (1/17) which gives 0 as it is again integer division.
Substituting the above result in the main equation we get this: select 10000/0. Since we have 0 in the denominator we have divide by zero error.
Thanks
17/17/17=0
1000/0= result is infinite so,Divide by zero error encountered.
because sql does not allows the infinite values
it 17/17 =1 and 1/17 will be 0 as it is int.
In (17/17/17) the first 17/17 evaluates first and returns 1.Next it will be (1/17) will return 0 ,as the values are integer.
To get the correct output you either use decimal conversion
SELECT 10000.00/(17.00/17.00/17.00) AS Result2;
select (17/17/17) this query execute first it returns zero .here value is rounded to zero because 17 is integer
select SELECT 10000/0 that’s why we got above error
I would guess that this is due to integer division resulting in an integer product coupled with order of operations.
10000 / (17 / 17 / 17)
First division within parentheses, 17/17 = 1
10000 / (1 / 17)
Integer division rounds down, so 1/17 = 0
10000 / 0
Divide by zero error.
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
17/17/17
This result integer value
Select 17/17 give result 1
Now Select 1/17 give result 0
So SELECT 10000/(17/17/17) AS Result2;
query it gives following error:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
the denominator part (17/17/17) = (1/17) = 0 (exact 0.0588).
same level of precedence of division operators so executing left to right and resulting to 0.
thus giving divide by zero error.
Hi Pinal,
When we evaluate the expression (17/17/17),it results to 0 as (17/17)=1 and 1/17 will be 0.05 but here 1 and 17 are integers decimal values are truncated and quotient will end up in 0.This leads to Divide by Zero error.
Thanks,
Sreelekha
Two reasons for the above exception are:
1. Operation execution order is BODMAS
2. Division of two inegers leads to INTEGER
BODMAS stands for Brackets, Orders (ie Powers and Square Roots, etc), Division, Multiplication, Addition and Subtraction.
The order is as follows:
Step1: 10000/(17/17/17) (paranthesis first)
Step2: 10000/(17/17/17) ( next operation is LEFT to RIGHT inside paranthesis, So division of 17/17 becomes 1)
Step3: 10000/(1/17) ( division of 1/17 becomes zero) and ( division of two integers return integer value 1/17 = 0; if you want to get float value from integers division, it should be as 1/(17*1.0)
Step4: 10000/0 returns Divided By Zero Exception….
SQL Executes first 17/17 which gives 0, after that it executes 1/17 that is rounded up to 0. after that it tries to divide 10000/0 which brings us to the error.
Hi,
SELECT 10000/(17/17/17) AS Result2
Here denominator is int.So “17/17/17” is rounded up to 0.
Hence the error.
If we just cast the denominator to decimal,error is gone.
SELECT 10000/(17/17/17.0) AS Result2;
Hi, it’s because of an implicit conversion to int.
This will work
SELECT CONVERT(numeric(12,2), 10000/17)/17/17 AS Result2;
Hi Dave,
I think it’s because of an implicit conversion to int.
this should work.
SELECT CONVERT(numeric(12,2), 10000/17)/17/17 AS Result2;
Maybe , it can be like this:
SELECT 10000/(convert(decimal(10,2),17/17)/17) AS Result2;
Division of Integer will return always integer value.
Express in parentheses is executed first and two same operator is executed from left to right,
As per this rules,
It is executed as,
10000/(17/17/17)
=10000/(1/17)
=10000/(0)
So, it is giving divided by 0 error.
Greetings Pinal !
I am doing in the following way.
SELECT 10000/(17/17/17) AS Result2;
Step 1 : Iam showing the denominator execution.
select 17/17 —> 1
step 2: select 1/17 ——>0
so SELECT 10000/(0) will give the Divide by zero…
Thank you,
Satish