Arithmetic overflow error converting float to data type numeric with positive exponents but not negative exponents

Question

My numeric column is set to (38,19), but I get bizarre arithmetic overflow errors.

What WORKS:

1a) INSERT INTO sen.numeric_table VALUES('MaxUBInt', 1844674407370955161);
1b) INSERT INTO sen.numeric_table VALUES('SmallestFloatBy10', 1.175494351e-39);

What DOESN'T WORK:

2a) INSERT INTO sen.numeric_table VALUES('MaxFloat', 3.402823466e+38);
2b) INSERT INTO sen.numeric_table VALUES('MaxFloatBy10', 3.402823466e+39);

What I don't understand:

1) The column should allow any input not exceeding a total of 38 digits as well as not exceeding 19 places after the decimal. Assuming this is true, why does (1b) work when I've clearly exceeded 38 total number of digits? (as well as exceeding 19 digits of precision after the decimal)

Thanks!

Answer 1

Underflow doesn't cause an error in SQL server; it will just round up. There are a few good discussions about it. This one for example:

http://social.msdn.microsoft.com/Forums/en-US/2e0ca122-ecca-4ec6-a60b-e3d4b9f51d94/underflow-does-not-cause-an-error?forum=transactsql

Answer 2

Based on your tests in 1) and 2), it looks like you're trying to find the upper bounds for int and float and lower bounds for float using the numeric(38,19) data type. The int bounds will be determined by the scale factor (19), and the precision bounds are defined for the numeric data type (see below).

Maximum precision in SQL Server is -10^38 +1 through 10^38 –1 according to MSDN. Minimum precision will round based on the value for NUMERIC_ROUNDABORT.

If this is a scientific application, you may want to read the MSDN description of precision, length, and scale, especially if you are performing operations with the values stored in the numeric column.