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. Consider it my small April Fools’ Day puzzle for you.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
20 Comments. Leave new
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.
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.
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.
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.
Thanks Pinal to introduce this typical question. Thanks Lohith to give the better explanation.
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
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
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 .
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
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
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
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
SELECT 30.0/-2.0/5.0 is evaluated as SELECT 30.0/-(2.0/5.0)
Really Amazing Puzzle
Thanks
Really amazing Post Sir
Thanks
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
Its 3 for both:
SELECT 30.0/(2.0)/5.0; – 3.000000000
SELECT 30.0/2.0/5.0; – 3.0000000000
i can’t understand how this puzzle is executing in SSMS
pls explain it pinal.
SELECT 30.0/(2.0)/5.0; – 3.000000000
SELECT 30.0/2.0/5.0; – 3.0000000000
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.