What Are the Underflow and Overflow Behaviors on FLOAT Literals?
Submitted by: AdministratorIf 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
Submitted by: Administrator
* 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
Submitted by: Administrator
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).
Submitted by: Administrator
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).
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | Can You Roll Back the DDL Statement in a Trigger? |
☺ | What Is a Subquery in a SELECT Query Statement in MS SQL Server? |
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |