One of my pet peeves about SQL Server is the decimal data type. Declaring it once in a table where you don’t have to do any math on it is normally okay, but I get very confused when I have to take two unknown values, do a mathematical operation on them and then store them into a table on the fly. This usually generates that oh so annoying: “Arithmetic overflow error converting [type] to data type numeric.”
Here is an example:
SQL:
CREATE TABLE tblDecimalTest
(
DecimalColumn dec(5,2) NOT NULL
);
INSERT INTO tblDecimalTest ( DecimalColumn ) VALUES(1.1); -- OK
INSERT INTO tblDecimalTest ( DecimalColumn ) VALUES(5); -- OK
INSERT INTO tblDecimalTest ( DecimalColumn ) VALUES(5.1234); -- OK
INSERT INTO tblDecimalTest ( DecimalColumn ) VALUES(99999); -- Int Conversion Exception
INSERT INTO tblDecimalTest ( DecimalColumn ) VALUES(99999.0); -- Numeric Conversion Exception
Messages:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
I found a trick to sort of avoid this kind of problem, it won’t always work if you are going to use it to preset precision, but it is a start to something better than just randomly deciding or trying to estimate the amount of precision that you will require. Take your inputs, typically a query, and store the virtual table into a new table. After the table is created highlight its name and press Alt + F1, this will give you the complete listing of a lot of information about the table that was just created. Most interestingly and most valuable it will tell you the dimensions of the decimal column that it has appropriated for you.
Example 2:
--This is just an example, it is taking all of the rows from the previous example and multiplying them by 20 SELECT decUnknownSize = DecimalColumn*20 INTO tblDecimalTest2 --Create a new table and NOT a temporary table because the trick won't work with a temp table FROM tblDecimalTest;
Screen Shot of Alt + F1
As we can see in the screen shot above the precision has been determined for us which helps you if you need to manually create the table for what ever reason. There is only one draw back to this scenario, this will only work if you know that the values you are working with will never be bigger than what has been specified already. Otherwise you are going to find yourself fudging with the numbers a lot, in which case you might be better off “SELECT INTO” a real temporary table (#/##) or a Common Table Expression because it will be created for you on the fly. This won’t work if you are declaring the schema of a table, temporary table or a table variable.
MSDN Link for Decimal and Numeric Data Type
http://msdn.microsoft.com/en-us/library/aa258832(SQL.80).aspx