MS SQL Server Interview Question:

What Happens When Converting Big Values to NUMERIC Data Types?

Submitted by: Administrator

If you are converting a numeric expression to a NUMERIC data type and the value is too big for the storage size, you will get an arithmetic overflow error as shown in the following examples:

-- Overflow error on implicit conversion
DECLARE @x FLOAT(24);
DECLARE @y NUMERIC(5,2);
SET @x = 12345.12E+00;
SET @y = @x;
GO
Msg 8115, Level 16, State 6, Line 4
Arithmetic overflow error converting real to data type
numeric.

-- Overflow error on explicit conversions
DECLARE @x FLOAT(24);
SET @x = 12345.12E+00;
SELECT CAST(@x AS NUMERIC(5,2));
SELECT CONVERT(NUMERIC(5,2),@x);
GO
Msg 8115, Level 16, State 6, Line 3
Arithmetic overflow error converting real to data type
numeric.
Msg 8115, Level 16, State 6, Line 4
Arithmetic overflow error converting real to data type
numeric.

Submitted by: Administrator