Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Underflow and Overflow Behaviors on FLOAT Literals

By: FYIcenter.com

(Continued from previous topic...)

What Are the Underflow and Overflow Behaviors on FLOAT Literals?

If you enter a floating number that is too big or too small for the FLOAT data type, SQL Server 2005 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.

The tutorial exercise below some good underflow and overflow examples:

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

-- Single precision overflow error
DECLARE @x REAL; -- FLOAT(24)
SET @x = 9.234568E+39;
GO
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;
GO
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
GO
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).

1. What Is a Constant or Literal?
2. How To Write Character String Constants or Literals?
3. What Is a Collation?
4. How To Specify the Collation for a Character Data Type?
5. What Happens If Strings Are Casted into Wrong Code Pages?
6. How To Find Out What Is the Default Collation in a Database?
7. How Fixed Length Strings Are Truncated and Padded?
8. How To Enter Unicode Character String Literals?
9. How To Enter Binary String Literals?
10. How To Enter Date and Time Literals?
11. Why I Can Not Enter 0.001 Second in Date and Time Literals?
12. What Happens If Date-Only Values Are Provided as Date and Time Literals?
13. What Happens If Time-Only Values Are Provided as Date and Time Literals?
14. What Are Out-of-Range Errors with Date and Time Literals?
15. What Happens If an Integer Is Too Big for INT Date Type?
16. How Extra Digits Are Handled with NUMERIC Data Type Literals?
17. How REAL and FLOAT Literal Values Are Rounded?
18. What Are the Underflow and Overflow Behaviors on FLOAT Literals?