What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL?

If you enter a floating number that is too big or too small for the FLOAT data type, Transact-SQL will behave as:

• FLOAT(24) Underflow: If a floating number is too small for FLOAT(24), it will be stored as 0 without any warning.
• FLOAT(24) Overflow: If a floating number is too big for FLOAT(24), you will get an arithmetic overflow error.
• FLOAT(53) Underflow: If a floating number is too small for FLOAT(53), it will be stored as 0 with a warning.
• FLOAT(53) Overflow: If a floating number is too big for FLOAT(53), you will get a value-out-of-range error.

Tutorial exercises below show some good underflow and overflow examples:

-- Single precision underflow without warning
DECLARE @x REAL; -- FLOAT(24)
SET @x = 9.234568E-39;
SELECT @x;
----------------------------------
0

-- Single precision overflow error
DECLARE @x REAL; -- FLOAT(24)
SET @x = 9.234568E+39;
----------------------------------
Msg 232, Level 16, State 2, Line 2
Arithmetic overflow error for type real, 
   value = 9234568000000000400000000000000000000000.000000.

-- Double precision underflow with warning
DECLARE @x FLOAT(53);
SET @x = 9.23456789012346E-309
SELECT @x;
----------------------------------
Warning: the floating point value '9.23456789012346E-309' 
   is too small. It will be interpreted as 0.
0

-- Double precision overflow error
DECLARE @x FLOAT(53);
SET @x = 9.23456789012346E+309
----------------------------------
Msg 168, Level 15, State 1, Line 2
The floating point value '9.23456789012346E+309' is out of 
   the range of computer representation (8 bytes).

 

⇒Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials