SQL SERVER – A Puzzle – Illusion – Confusion – April Fools’ Day

Today is April 1st and just like every other year, I like to bring something interesting and light for the day. Atleast there should be days in every one’s life when they should feel easy. Here is a quick puzzle for you and I believe it will make you feel extremely smart if you can figure out the result behind the same.

Run following in SQL Server Management Studio and observe the output:

SELECT 30.0/(-2.0)/5.0;
SELECT 30.0/-2.0/5.0;

Here are few questions for you:

1) What will be the result of above two queries?
2) Why?

If you think you can figure out the result without executing them – I encourage you to execute BOTH of them in SSMS and see if they give you same result or different result.

Well, now I am waiting for your answer here – why? I often post similar things on my facebook page – you are welcome to play with me there.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012
Next Post
SQL SERVER – Use ROLL UP Clause instead of COMPUTE BY

Related Posts

20 Comments. Leave new

  • Shankar Jayaraman
    April 1, 2012 7:41 am

    Pinal – After attending your session in TechEd, I worked on the problem given on Sql Server Misconception & Resolution and I am taking a guess without executing the query. Q1: -3 & Q2: -75. Reason: The / and * toggle after a minus operator! Thanks.

    Reply
  • Amazing Puzzle, here’s my attempt to crack this.

    –Here;
    –SELECT 30.0/(-2.0)/5.0 is evaluated as SELECT (30.0/(-2.0))/5.0 = -3.000000000
    –Parentheses are taking precedence over unary minus

    –In contrast;
    –SELECT 30.0/-2.0/5.0 is evaluated as SELECT 30.0/(-2.0/5.0) = -75.0000000000

    Thanks.

    Reply
  • Lohith KS Chalam
    April 1, 2012 6:01 pm

    SELECT 30.0/(-2.0)/5.0;
    Answer: -3.000000000

    SELECT 30.0/-2.0/5.0;
    Answer: -75.0000000000

    Reason: The unary minus takes precedence over division operator when it is not included in parentheses. This is known bug in SQL server.

    Reply
  • Sir, I always read your blogs and they are very interesting but as i am in developer field so i also need to learn C#, VB.Net,APS.NET,Jquery,HTML,CSS.
    So can you plz tell me the sits like sqlauthority.com from where i can learn above language.
    Plz help me out waiting for your reply…
    Thank You.

    Reply
  • Thanks Pinal to introduce this typical question. Thanks Lohith to give the better explanation.

    Reply
  • This is easy. Funny is what SQL server returns for

    SELECT ROUND(0.7, 0) (at least for SQL 200 R2 SP1, not sure if it is still broken in 2012).

    PS: ivarund – your code is not correct, it is

    –SELECT 30.0/-2.0/5.0 is evaluated as SELECT (30.0 / -(2.0 / 5.0)) – minus is oustide

    Reply
  • ivarund – it it not exactly right

    SELECT 30.0/-2.0/5.0 is evaluated as SELECT (30.0/-(2.0/5.0))

    That was easy. More funny is

    SELECT ROUND(0.7, 0) — just let server do some basic round

    This “works” in SQL2008 R2 SP1, not sure if MS fixed it in Denali

    Reply
  • Kutti Krishnan
    April 2, 2012 9:28 am

    Hi Pinal,

    I have tried both the puzzle in SSMS. Even I also got the same answer :) ,

    SELECT 30.0/(-2.0)/5.0; — -3.000000000
    SELECT 30.0/-2.0/5.0; — -75.0000000000

    * The First one looks pretty simple ,so there is no question of asking how come it is “-3.00”. It is a simple mathematics.

    * But in the case of second one, it seems to be bit tricky . Some how it is executing like “30.0/(-2.0)*5.0;” . But I am not sure the reason .

    Reply
  • sheikmydheen
    April 2, 2012 9:50 am

    SELECT 30.0/(-2.0)/5.0;

    Ans will be :-3.000000000

    SELECT – 30.0/2.0/(5.0);

    Ans will be :-3.000000000

    SELECT 30.0/2.0/(-5.0);

    Ans will be :-3.000000000

    Reply
  • SQL gives higher priority to those figures who are in parenthesis it solves the problem comes in parenthesis first and then rest so:

    SELECT 30.0/(-2.0)/5.0; it

    Ans will be :-3.000000000

    SELECT 30.0/-2.0/5.0;

    Ans will be :-75.000000000

    Reply
  • Hi Pinal,

    Thanks for sharing this. Finally i solved this with the great help of my senior DBA person. I got the reason behind this.

    Thx
    Vinay

    Reply
  • Mathematical equation should be carried out using BEDMAS login
    where BEDMAS stands for
    B = Brackets
    E = Exponent
    D = Division
    M = Multiplication
    A = Addition
    S = Subtraction

    Reply
  • Bhavesh Myangar
    April 2, 2012 3:08 pm

    SELECT 30.0/-2.0/5.0 is evaluated as SELECT 30.0/-(2.0/5.0)

    Reply
  • passionateashu
    April 3, 2012 10:10 am

    Really Amazing Puzzle

    Thanks

    Reply
  • passionateashu
    April 3, 2012 10:15 am

    Really amazing Post Sir

    Thanks

    Reply
  • Parth Malhan
    April 6, 2012 2:52 pm

    Please use these statement and have fun:
    By removing – we get the same result.
    WHY?

    SELECT 30.0/(2.0)/5.0; – 3.000000000
    SELECT 30.0/2.0/5.0; – 75.0000000000

    Reply
  • Parth Malhan
    April 6, 2012 2:53 pm

    Its 3 for both:

    SELECT 30.0/(2.0)/5.0; – 3.000000000
    SELECT 30.0/2.0/5.0; – 3.0000000000

    Reply
  • keval mehta
    May 3, 2012 2:59 pm

    i can’t understand how this puzzle is executing in SSMS
    pls explain it pinal.

    Reply
  • SELECT 30.0/(2.0)/5.0; – 3.000000000
    SELECT 30.0/2.0/5.0; – 3.0000000000

    Reply
  • Hi Pinal Sir,
    First one is executing by the left hand over which parenthesis operator have hig ht priority so it will gives as 30.0/(-2.0)/5.0 will give as 30.0/(-2.0)=-15.0/.0=-3.0
    so for second example 30.0/-2.0/5.0 will executed as -2.0/5.0 as unary operator have higher precedence so it will be executed as -2.0/5.0 give -0.4 then we have 30/-0.4 so it will give output as -0.75.

    Reply

Leave a Reply

Menu