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)**

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