CODEWITHSUNDEEP
sql-servert-sqlcasting
Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

What are the limits of converting int to real

When I write:

SELECT CAST (CAST (32652317 as real) as int)

The result is:

32652316

What is the maximum integer number to be safely stored within a column of type real without a risk of getting wrong value?

Upvotes: 0

Views: 470

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Just for fun:

The following code will return the first number, where your cast and the original number are different.

WITH x AS(SELECT 1 AS N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(N))--10^1
    ,N3 AS (SELECT 1 AS N FROM x CROSS JOIN x AS N2 CROSS JOIN x N3) --10^3
    ,Tally AS(SELECT (ROW_NUMBER() OVER(ORDER BY(SELECT NULL))) AS Nr FROM N3 
              CROSS JOIN N3 N6 CROSS JOIN N3 AS N9)
SELECT TOP 1 Nr,CAST (CAST (Tally.Nr AS REAL) AS INT)
FROM Tally
WHERE Nr<>CAST (CAST (Tally.Nr AS REAL) AS INT);

My result was 16777217 and 16777216

UPDATE

With higher numbers the difference increases. The following looks for the first where the differenc is higher than 2:

WITH x AS(SELECT 1 AS N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(N))--10^1
    ,N3 AS (SELECT 1 AS N FROM x CROSS JOIN x AS N2 CROSS JOIN x N3) --10^3
    ,Tally AS(SELECT (ROW_NUMBER() OVER(ORDER BY(SELECT NULL))) AS Nr FROM N3 
              CROSS JOIN N3 N6 CROSS JOIN N3 AS N9)
SELECT TOP 1 Nr,CAST (CAST (Tally.Nr AS REAL) AS INT)
FROM Tally
WHERE ABS(Nr-CAST (CAST (Tally.Nr AS REAL) AS INT))>2

My result was 67,108,867 and 67,108,864

Asked for >5 I got 134,217,734 and 134,217,728

UPDATE: Float vs. Real

If you replace the REAL with FLOAT, there won't be any allowed INT which breaks your cast, but this takes 8 byte storage.

According to this page REAL is treated as FLOAT(24) taking 4 bytes. 24 of 32 bits are used for the mantissa. FLOAT expects an INT-parameter to set its width. FLOAT(24) is far below the max width. Omitting this parameter is defaulted with the max value 53, but taking 8 bytes.

Even with FLOAT(1) at least 1 bit is reserved for the mantissa. When the 7th byte is "begun", the rounding begins. This happens 1 number below 16777217:

Byte 7 is begun

Upvotes: 4

Related Questions