11

MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column with 1.401298E-45:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 1.18E-37, 10 ) ;
GO
DECLARE @i INT ;
SET @i = 1 ;

WHILE @i < 20 
  BEGIN ;

    UPDATE  a
    SET     r1 = r1 / r2 ;

    SELECT  r1 ,
            r2
    FROM    a ;

    SET @i = @i + 1 ;

  END ;
GO
DROP TABLE a ;

r1            r2
------------- -------------
1.18E-38      10

(snip)

r1            r2
------------- -------------
1.401298E-45  10

Can anyone tell me what is the actual lowest possible positive number?

  • 1
    There's definitely some weird behaviour with this type. The smallest scalar literal I was able to cast and select was 1.1754944E-38, which came back as 1.175494E-38 (note the missing 4 at the end) -- if you try to cast 1.175494E-38 directly, you get zero back. – Jon Seigel Jul 3 '12 at 2:18
13

The minimum positive (subnormal) single-precision floating-point value is 2−149 ≈ 1.4 × 10−45. The minimum positive normal value is 2−126 ≈ 1.18 × 10−38 (reference).

DECLARE
    @r1 real = POWER(2e0, -126),
    @r2 real = POWER(2e0, -23)

SELECT
    @r1,
    @r2,
    @r1 * @r2,
    CONVERT(binary(4), @r1 * @r2);

For double-precision, the minimum positive subnormal is:

DECLARE @r1 float = POWER(2e0, -1075);

SELECT @r1, CONVERT(binary(8), @r1);
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.