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 http://facebook.com/SQLAuth – you are welcome to play with me there.

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

About these ads

21 thoughts on “SQL SERVER – A Puzzle – Illusion – Confusion – April Fools’ Day

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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

  6. 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

  7. 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 .

  8. 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

  9. 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

  10. 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

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  12. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s