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 (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com
Next Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31

Related Posts

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.

    Reply
  • Varinder Sandhu
    June 30, 2011 12:33 pm

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

    Reply
  • 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

    Reply
  • Mostafa Elmasry
    June 30, 2011 1:36 pm

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

    Reply
  • 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)
    Cant Read This Number
    So the diffreent between Puzzle 1 and Puzzle 2
    is buzzel 1 use (minus) buzel 2 use (plus)
    ———————————————————————————

    Reply
  • hi
    pinal sir.

    how many primary key we can use in single table.

    Reply
  • 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 “

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

    Reply
  • 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 “

    Reply
  • 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
    ———————

    Reply
  • 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…

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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

    Reply
  • Thank you all for excellent participation.

    As this competition was to post a comment and solution on facebook page – the winner will be announce there only.

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

    Reply
  • gabriel sunday
    August 4, 2011 6:50 pm

    i am very talented in statitical or very goood in mathematic

    Reply
  • nice article

    Reply

Leave a Reply