Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
I've asked about this in channels I know you're familiar with, but all I hear so far is crickets. This might be a bad week to ask due to the July 4th holiday. –  Aaron Bertrand Jul 2 '12 at 21:00
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
    
@AaronBertrand thank you for looking into it, Aaron! –  AlexKuznetsov Jul 3 '12 at 18:25
    
@AlexKuznetsov meh, I didn't do much. Basically repeated your question and got a couple of the Pauls interested enough to share their knowledge. :-) –  Aaron Bertrand Jul 3 '12 at 18:27

1 Answer 1

up vote 8 down vote accepted

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:

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

SELECT @r1, CONVERT(binary(8), @r1);
share|improve this answer
1  
+1: Haven't heard about subnormal numbers yet. –  MicSim Jul 3 '12 at 7:01
1  
Me neither. I knew surreals but had never heard of subnormal ones, till today. –  ypercube Jul 3 '12 at 10:44
    
This makes sense Paul. Thanks! –  AlexKuznetsov Jul 3 '12 at 18:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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