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
- 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 (https://blog.sqlauthority.com)
18 Comments. Leave new
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.
I have marked this answer on facebook to under
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).
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
I have marked this answer on facebook to under
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)
———————————————————————————
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)
———————————————————————————
hi
pinal sir.
how many primary key we can use in single table.
Only one. However you can have it on many columns as a composite primary key
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 “
Just wanted to say that is an EPIC picture in your header.
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 “
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
———————
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…
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
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.
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
i am very talented in statitical or very goood in mathematic
nice article