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 = 9000000000000000**6**000000000000000000000000.000000.

## How to participate

- To win the Gift Card USD 25 you will have to answer both of the question on my
**Facebook page**. - 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.

Related Post: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com

Reference:** Pinal Dave (****http://blog.SQLAuthority.com****)**

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.

LikeLike

I have marked this answer on facebook to under http://www.facebook.com/SQLAuth

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

LikeLike

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

LikeLike

I have marked this answer on facebook to under http://www.facebook.com/SQLAuth

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)

Cant Read This Number

So the diffreent between Puzzle 1 and Puzzle 2

is buzzel 1 use (minus) buzel 2 use (plus)

———————————————————————————

LikeLike

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)

Cant Read This Number

So the diffreent between Puzzle 1 and Puzzle 2

is buzzel 1 use (minus) buzel 2 use (plus)

———————————————————————————

LikeLike

hi

pinal sir.

how many primary key we can use in single table.

LikeLike

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

LikeLike

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 “

LikeLike

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

LikeLike

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 “

LikeLike

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

———————

LikeLike

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…

LikeLike

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

LikeLike

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.

LikeLike

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

LikeLike

Thank you all for excellent participation.

As this competition was to post a comment and solution on facebook page http://www.facebook.com/SQLAuth – the winner will be announce there only.

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

LikeLike

i am very talented in statitical or very goood in mathematic

LikeLike

nice article

LikeLike