# SQL SERVER – Two Puzzles – Answer and Win USD 25 Gift Card

Today I have two simple T-SQL Puzzle. You can answer them and win USD 25 Gift card. The gift card will be sent in email to winner. You will get choice of Gift Card brand based on your preference and country location.

### Puzzle 1: What will be the outcome and why?

```DECLARE @x REAL; SET @x = 9E-40 SELECT @x; ```

The outcome here is obvious as I have used negative number in assignment. What is the reason behind the same?

### Puzzle 2: Why will be the outcome different from Puzzle 1:

```DECLARE @y REAL; SET @y = 9E+40 SELECT @y;```

The outcome of this puzzle very different from puzzle 1  as I have used positive number. There is number six (6) in the resultset why?

Msg 232, Level 16, State 2, Line 2
Arithmetic overflow error for type real, value = 90000000000000006000000000000000000000000.000000.

## How to participate

1. To win the Gift Card USD 25 you will have to answer both of the question on my Facebook page.
2. If you are on twitter – you can increase the chance of winning by tweeting your participation.

This contest is open for any one from any country. The winner will be selected Randomly. Winner will be announced on July 7, 2011.

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

#### Related Posts

• Vinay Kumar
June 30, 2011 12:20 pm

ISO synonym for real is float(24) and real range is [- 3.40E + 38 to -1.18E – 38]. So

1. If a real number is too small, it will be stored as 0 without any warning.

2. If a real number is too big for FLOAT(24), you will get an arithmetic overflow error.

• Varinder Sandhu
June 30, 2011 12:33 pm

Answer of Puzzle 1 and 2

The float and real data types are known as approximate data types. Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value.
9E-40 means 9 x 10-40

As real data type is an approximate data type, and the range of real data type is 1.18E – 38, this result is approximated to 0.

Just like this in other case 9 x 1040 cannot be approximated, as maximum value that can be stored in real is 3.40E + 38 (3.40 x 1038).

• Varinder Sandhu
June 30, 2011 12:54 pm

above in comment

9 x 10-40 means 9 x 10 raise to power -40
9 x 1040 means 9 x 10 raise to power 40

• Mostafa Elmasry
June 30, 2011 1:36 pm

Answer of Puzzle 1 and 2
Puzzle 1
E = 0 So if i you make the mark is Minus (-)thats mean the the zero (0) will put before the number(9)
So (9E – 40 ) = (0,000000000000000000000000000000000000000000000…9)
Example
9E-1 Will return (0,9) becouse E = 0 and the mark is Minus and the number after the Minus is (1) so we
will put (0) the 9
9E-2 Will return (0,09)
becouse E = 0 and the mark is Minus and the number after the Minus is (1) so we
will put two(0) the 9
So in the buzzel 1 return zero becouse the sql not read 40 zero before number 9 and
any calculater not read this number
———————————-
Puzzle 2
E=0 But in this case i make the mark is plus (+) so any number will be plus on the 9E
thats means this number is the Count of Zero
Examble
9E+1 ( E=0, 1 Mean (Put one zero ) So the result is 90
9E+2 (E=0 , 2 mean (put two Zero ) So the result is 900
So
9E +40 (Must be return [Arithmetic overflow error for type real, value = 90000000000000006000000000000000000000000.000000.
] Becouse sql cant have read this big number (9+40 Zero)
So the diffreent between Puzzle 1 and Puzzle 2
is buzzel 1 use (minus) buzel 2 use (plus)
———————————————————————————

• Mostafa Elmasry
June 30, 2011 1:41 pm

Answer of Puzzle 1 and 2
Puzzle 1
E = 0 So if i you make the mark is Minus (-)thats mean the the zero (0) will put before the number(9)
So (9E – 40 ) = (0,000000000000000000000000000000000000000000000…9)
Example
9E-1 Will return (0,9) becouse E = 0 and the mark is Minus and the number after the Minus is (1) so we
will put (0) the 9
9E-2 Will return (0,09)
becouse E = 0 and the mark is Minus and the number after the Minus is (1) so we
will put two(0) the 9
So in the buzzel 1 return zero becouse the sql not read 40 zero before number 9 and
any calculater not read this number
———————————-
Puzzle 2
E=0 But in this case i make the mark is plus (+) so any number will be plus on the 9E
thats means this number is the Count of Zero
Examble
9E+1 ( E=0, 1 Mean (Put one zero ) So the result is 90
9E+2 (E=0 , 2 mean (put two Zero ) So the result is 900
So
9E +40 (Must be return [Arithmetic overflow error for type real, value = 90000000000000006000000000000000000000000.000000.
] Becouse sql cant have read this big number (9+40 Zero)
So the diffreent between Puzzle 1 and Puzzle 2
is buzzel 1 use (minus) buzel 2 use (plus)
———————————————————————————

• Tejpratap
June 30, 2011 5:51 pm

hi
pinal sir.

how many primary key we can use in single table.

• June 30, 2011 7:00 pm

Only one. However you can have it on many columns as a composite primary key

• Abdul Awwal
June 30, 2011 6:20 pm

The range of real datatype in sql server is from -3.40E + 38 to 3.40E + 38 . Any number which does not fall in this range is cause “Arithmetic overflow error ”

For Puzzle 1 =>

9E-40 mean 9 * 10 raise to the power -40
that means 9/10 raise to the power 40 which is almost zero

For Puzzle 2 =>
The value 9E+40 is higher than “3.40E + 38” so it cause “Arithmetic overflow error “

• baki
June 30, 2011 9:13 pm

Just wanted to say that is an EPIC picture in your header.

• Bablu
July 1, 2011 9:54 am

The range of real datatype in sql server is from -3.40E + 38 to 3.40E + 38 . Any number which does not fall in this range is cause “Arithmetic overflow error ”

For Puzzle 1

9E-40 mean 9 X 10 raise to the power -40
that means 9/10 raise to the power 40 which is almost zero

For Puzzle 2
The value 9E+40 is higher than “3.40E + 38″ so it cause “Arithmetic overflow error “

• Yuvraj Gautam
July 1, 2011 3:00 pm

Have a glance on it

———————
DECLARE @x1 REAL;
DECLARE @x2 REAL;
DECLARE @x3 REAL;– Result1
DECLARE @x4 REAL;– Result2

set @x2 = 4
SET @x1 = 3
set @x3 = @x2/@x1;–Consider as Real
set @x4 = 4/3;–Consider as Integer

select @x3
select @x4
———————

• Ajit
July 1, 2011 3:21 pm

Simple thing is… “range for numeric representation (maximum precision 38)”

Try below ,

select 123456789012345678901234567890123456780
Check Message:
Msg 1007, Level 15, State 1, Line 2
The number ‘123456789012345678901234567890123456780’ is out of the range for numeric representation (maximum precision 38).

Now you remove last 0 from select as below and try again,

select 12345678901234567890123456789012345678

It will result…. : 12345678901234567890123456789012345678

Hope it clear everything…

• Yuvraj Gautam
July 1, 2011 7:13 pm

The REAL data type range is = – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38

Puzzle 1 (9E-40)
in the case of 9E-40 = 0.0000000000000000000000000000000000000009. This will not through an error or message. this will show 0 after 9E-38.
This is printing 0 Because given expression 9E-40 is in the range of real.
(It reads 0.00000000000000000000000000000000000000 and prints 0 )

Puzzle 2 (9E+40)
in the case of 9E+40 = 90000000000000000000000000000000000000000 , Will through an errror because The Value is out of range.

use Float to find desired answer [Note The ISO SYNONYM for REAL is FLOAT(24)]
Puzzle 1 (9E-40)
DECLARE @x float(25);
SET @x = 9E-40
SELECT @x; — prints 9E-40

Puzzle 2 (9E+40)
DECLARE @y float(25);
SET @y = 9E+40
SELECT @y; –prints 9E+40

• Yuvraj Gautam
July 2, 2011 2:36 pm

There is number six (6) in the resultset ,I think when system was calculating result that time overflow occured and this printed six(6)
The process was in mid and overflow occured.
e.g.
SELECT POWER(10,22) –10000000000000000000000.000000.
SELECT POWER(10,23) –100000000000000010000000.000000.
SELECT POWER(10,24) –999999999999999980000000.000000.

• Ravishanker
July 6, 2011 9:17 am

for 2nd question
2) Puzzle 2: Why will be the outcome different from Puzzle 1:

DECLARE @y REAL;
SET @y = 9E+40
…SELECT @y;

the Number is 90000000000000000000000000000000000000000

and the size real data type allows is 4 Bytes
and the limit of value lies in the below
– 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38

for 1st question
2) Puzzle 1: Why will be the outcome different from Puzzle 1:

DECLARE @y REAL;
SET @y = 9E-40
…SELECT @y;

the Number is 0.00000000000000000000000000000000000000009

and the 9 number after 40 zeros in decimal places, we will represent it as 9e-40 which we will take as zero

• July 10, 2011 6:22 am

Thank you all for excellent participation.

Many thanks for participating it is really interesting question and I have received many valid answer.

• gabriel sunday
August 4, 2011 6:50 pm

i am very talented in statitical or very goood in mathematic

• 