SQL SERVER – Puzzle – Why Divide by Zero Error

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.

SQL SERVER - Puzzle - Why Divide by Zero Error zeroerrorpuzzle

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)

Previous Post
SQL SERVER – Microsoft Releases for December 2014
Next Post
SQL SERVER – UNION and UNION ALL with TEXT DataType – Observation

Related Posts

No results found.

36 Comments. Leave new

  • Hi Pinal,
    Denominator will be converted into 0.05882… by compiler so

    17/17=1
    1/17=0.05882… (decimal value.)
    when we divide this decimal value it gets cast as an INT and rounds to 0.

    So ultimately
    10000/0 will give us ‘divide by zero’ error.

    Thanks,
    Vivek Chandel

    Reply
  • Hi Pinal,
    (17/17/17) = 1/17 = 0 >> It’s an integer division (rounded up)
    So 10000/0 is not permitted division, why the error message

    Reply
  • It is “SELECT 10000/(1/17) AS Result2;” which execute as “SELECT 10000/0 AS Result2;”
    That is why giving divide by zero error envountered.

    Reply
  • (17/17/17)
    17/17=1 => It starts executing from left to right
    1/17=0 => Result with in the bracket becomes zero

    10000/(17/17/17)
    10000/0 = ERROR !!

    Reply
  • Manish Kumar Choudhary
    December 29, 2014 6:07 pm

    Actually 17/17/17 return Zero so it is returning error.
    17/17=1
    1/17=0 (when 1/17 it gives reminder 1 and result 0)
    1%17=1 (reminder 1)
    this is the reasion it is returing 0
    Actually 17/17/17 return Zero so it is returning error.
    17/17=1
    1/17=0
    1%17=1

    because actuality SELECT 10000/(0) AS Result2; so this error is coming

    Reply
  • Rohit Kalmankar
    December 29, 2014 6:08 pm

    Hello,

    I will go step-by-step.

    Firstly, the result of the SELECT (17/17/17) is – 0.

    Why?

    Arithmetically, it will perform this operation like below (internally) –

    select (17/17/17/1)

    Output: 0

    Reason: We are performing the integer division.

    If we run the below statement with decimal point values, here’s the result. (To contrast)

    select 1.0/17.0

    Output: 0.058823

    Hope this is clear enough. Let me know if you need more explanation on this.

    Secondly, we are performing,

    SELECT 10000/(17/17/17) AS Result2

    In short, it is an integer division, and we are dividing 10000/0 (as explained above). So, it will throw the below message.

    “Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.”

    We can avoid this using below statement. (There are more ways, but this is just an example)

    Example 1:

    SELECT 10000.0/(17.0/17.0/17.0) AS Result2

    Output: 170000.0000340000000068

    Example 2:

    SELECT 10000/17/(17/17) AS Result2

    Output: 588

    Thanks,
    Rohit Kalmankar

    Reply
  • Simple and sober answer:

    BODMAS: Order of Operation

    B-Brackets first
    O-Orders (ie. Powers and Square Roots, etc.)
    DM-Division and Multiplication (left-to-right)
    AS-Addition and Subtraction (left-to-right)

    Now no need to explain further.

    Thank you.

    Reply
  • you added () in denominator. so it will take presidency first and perform operation 17/17/17 which is 1/17 equal 0.058 but its integer only so it result 0. so now equation become like 1000/0 will hit divide by zero error.

    Below expression will not give an error.

    select 1000/17/17/17/17
    OR
    select 1000/(17/17/17.0) –> 1000/0.058823 –> it will work without error.

    Thanks you for sharing puzzle. keep sharing

    Thanks
    Rikhil

    Reply
  • Hi,

    As per operator preedence, SQL will process brackets info first from left to right.

    In bracket we have (17/17/17), it will process like (17/17)/17= (1)/17 = 0.

    If I break expression , it will be like 10000/((17/17)/17)

    Reply
  • As it will convert Select (17/17/17) to integer which will come as 0.so the error is coming.
    if we simply use (17/17/17.0), it will not give erro because now the sql server will converting the values in decimal.

    Reply
  • Due to the datatype of 17 being an int, causing loss of data when dividing.

    Declare @Decimal Decimal(18,4) = 17
    Declare @Int Int = 17

    SELECT 10000/(@Decimal/@Decimal/@Decimal) –Works Fine
    SELECT 10000/(@Int/@Int/@Int) –Devide by zero

    17/17/17 = 0.0588235294117647

    Reply
  • Balasubramanian Kumaran
    January 6, 2015 12:25 pm

    The ‘int’ data type has a precision of 10, scale of 0 and length of 4. If an arithmetic operator has two expressions of same type, the result has the same data type with the precision and scale defined for that concerned data type.

    Hence, applying BODMAS for an expression 1000 / (17/17/17), it becomes 1000 / (1/17).
    Now, 1/17 has a result of 0.0588235; Since both numerator and denominator are of type ‘int’, having the scale of 0. Hence, the result becomes 0.

    Finally, the expression becomes 1000 / 0 resulting division by zero error.

    Thanks && Regards,
    Balasubramanian Kumaran

    Reply
  • By applying BODMAS, the expression 1000 / (17/17/17) becomes 1000 / (1/17).

    The int data type has a precision of 10, a length of 4, and a scale of 0. If an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type.

    Hence, 1/17 returns 0 though it actually results 0.05. It is because of integer type has a scale of 0.

    Now, the expression becomes 1000 / 0 which results in division by zero error.

    Thanks && Regards,
    Balasubramanian Kumaran

    Reply
  • Guys, i discovered something that looks like sql bug, it gives division by zero where it should not be possible. I checked tables for integrity and no errors was found. Here is the smallest query i could get, if i remove any of the components it starts working, it is enough to remove order by and it works. Here is the query generating division by 0:

    SELECT CONVERT(VARCHAR(1), startTime), COUNT(DISTINCT vi), 1 / SUM(1)
    FROM Archive AS V
    INNER JOIN ctCategory AS C ON C.ID = V.categoryID
    GROUP BY CONVERT(VARCHAR(1), startTime)
    UNION
    SELECT CONVERT(VARCHAR(1), startTime), COUNT(DISTINCT vi), 1 / SUM(1)
    FROM Archive
    GROUP BY CONVERT(VARCHAR(1), startTime)
    ORDER BY 1

    Query makes little sense as i removed as many things as possible, now it is at the state where you can remove any of the fields or conditions or order or union and it will start working without error. Thing is 1/ SUM(1) should never give division by zero. Just wondering what do you think about it?

    Reply
  • Abhinav Pandey
    August 17, 2016 3:45 pm

    SELECT (10000)/(17.0/17/17) AS Result2;

    Reply
  • need to do
    SELECT 10000/convert(numeric(18,2),@i)/convert(numeric(18,2),@j)/convert(numeric(18,2),@k) AS Result2;

    Reply

Leave a Reply

Menu