Why SQL Server throws Arithmetic overflow error converting int to data type numeric?

I have an error being thrown by SQL Server Management Studio when running this code:

declare @percentage numeric(3,2)
set @percentage = cast(15 as numeric(3,2))

but when I change numeric declaration to 

declare @percentage numeric(4,2)
set @percentage = cast(15 as numeric(4,2))

everything goes fine.

Is there a limitation for numeric data type?

206667 次浏览
小开
最佳答案

Numeric defines the TOTAL number of digits, and then the number after the decimal.

A numeric(3,2) can only hold up to 9.99.

小开

Lets see, numeric (3,2). That means you have 3 places for data and two of them are to the right of the decimal leaving only one to the left of the decimal. 15 has two places to the left of the decimal. BTW if you might have 100 as a value I'd increase that to numeric (5, 2)

小开

NUMERIC(3,2) means: 3 digits in total, 2 after the decimal point. So you only have a single decimal before the decimal point.

Try NUMERIC(5,2) - three before, two after the decimal point.

小开

Precision and scale are often misunderstood. In numeric(3,2) you want 3 digits overall, but 2 to the right of the decimal. If you want 15 => 15.00 so the leading 1 causes the overflow (since if you want 2 digits to the right of the decimal, there is only room on the left for one more digit). With 4,2 there is no problem because all 4 digits fit.

小开

Pretty old question but I thought I'd add this here for completion sake: There is a SQL Server database setting which can affect queries and throw this error.

SET NUMERIC_ROUNDABORT { ON | OFF }

Setting this to ON will cause this exact error is the result does not fit the resulting datatype.

For example, setting this to ON and running

SELECT cast(0.0 / 1 AS NUMERIC(4,2))

will throw this error.

More Info: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-numeric-roundabort-transact-sql

Also another way to set it: poperties page


提交答案