MS SQL Server Concepts and Programming Question:

Download Questions PDF

Answer #1
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

Answer #2
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).