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.

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

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

About these ads

18 thoughts on “SQL SERVER – Two Puzzles – Answer and Win USD 25 Gift Card

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

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

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

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

  5. 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 “

  6. 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 “

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

  8. 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…

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s