Geek asks and answers

REAL column values ​​outside of the documented range

According to MSDN, the range of REAL values ​​is 3.40E + 38 to -1.18E - 38, 0 and 1.18E - from 38 to 3.40E + 38. However, I have quite a few values ​​outside this range in my table.

The following query returns many very small values ​​and not very large:

SELECT  MyColumn ,
        *
FROM    data.MyTable
WHERE   MyColumn <> 0
        AND ( MyColumn < CONVERT(REAL, 1.18E-38)
              OR MyColumn > CONVERT(REAL, 3.40E+38)
            )
        AND ( MyColumn < CONVERT(REAL, -3.40E+38)
              OR MyColumn > CONVERT(REAL, -1.18E-38)
            )

It is easy to show how these values ​​get into the table. I cannot insert them directly:

CREATE TABLE a(r REAL NULL);
GO
INSERT INTO a(r) VALUES(4.330473E-39);
GO
SELECT r FROM a
GO
DROP TABLE a;

----
0.0

But I can separate the two columns and get and outside the range value:

CREATE TABLE a
  (
    r1 REAL NULL ,
    r2 REAL NULL ,
    r3 REAL NULL
  ) ;
GO
INSERT  INTO a
        ( r1, r2 )
VALUES  ( 4.330473E-38, 1000 ) ;
GO
UPDATE  a
SET     r3 = r1 / r2 ;
SELECT  r1 ,
        r2 ,
        r3
FROM    a

r1            r2            r3
------------- ------------- -------------
4.330473E-38  1000          4.330433E-41

So, I assume that MSDN is giving incorrect ranges of valid data, right? Did I miss something?

Several people have suggested that this is a mistake.

What part of this behavior is exactly a mistake. this:

  • , MSDN DBCC, .
  • ,
+5
2

. IEEE 754 , , - denormalized, denormal subnormal. :

, ; . , a-b . , , , .

SQL Server . , DBCC .

, :

DECLARE
    @v1 real = 14e-39,
    @v2 real = 1e+07;

-- 1.4013e-045
SELECT @v1 / @v2;

, float, DBCC :

CREATE TABLE dbo.b (v1 float PRIMARY KEY);
INSERT b VALUES (POWER(2e0, -1075));
SELECT v1 FROM b; -- 4.94065645841247E-324
DBCC CHECKTABLE(b) WITH DATA_PURITY; -- No errors or warnings
DROP TABLE dbo.b;
+7

SQL Server. script . :

DBCC CHECKDB WITH data_purity

:

Msg 2570, 16, 3, 1 (1: 313), 0 ID 357576312, 0, 1801439851932155904, 2017612634169999360 ( " " ). "r3" "". .

, . Microsoft Connect SQL Server.

0

More articles:


All Articles