Sql-server – Arithmetic overflow error converting numeric to data type numeric (SQL Server). But the columns are big enough to contain data

sql server

We are receiving the following error when inserting our data into a SQL Server table:

Arithmetic overflow error converting numeric to data type numeric

The data we are inserting looks like this:

INSERTED.ID VALUES(0, 'Blah Blah', 6202, 2, -697937977842128, -179239499006078, 529122280867677, -682697977842128, -163999499006078, 529445721858303, -690317977842128, -171619499006078, 52928400136299, 1000)

But the column definitions for the big numbers are all Decimal(18,9) so I am not sure why we are getting this error.

Best Answer

As mustaccio pointed out, when you define a DECIMAL or NUMERIC data type in SQL Server, the first parameter in the declaration is the precision meaning how many total digits between both sides of the decimal point can be stored. The second parameter is the scale which is how many of those total digits can be stored to the right side of the decimal. (Therefore the total number of digits that can be stored to the left side of the decimal is the precision - scale.)

You can read more information about those data types in the Microsoft Docs - decimal and numeric (Transact-SQL).