SQL Server cast fails with arithmetic overflow

Question

According to the entry for decimal and numeric data types in SQL Server 2008 Books Online, precision is:

p (precision) The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

However, the second select below fails with "Arithmetic overflow error converting int to data type numeric."

SELECT CAST(123456789 as decimal(9,0))
SELECT CAST(123456789 as decimal(9,1))

Answer 1

Correct. Since you're doing decimal(9,1) that means you have 9 total digits, but the ,1 is reserving one of them for the right of the decimal place, so you can do at most 8 to the left and 1 to the right.

Answer 2

try
SELECT CAST(123456789 as decimal(10,1))

Answer 3

see here: http://msdn.microsoft.com/en-us/library/aa258832(SQL.80).aspx

decimal[(p[, s])]

p (precision) Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale) Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

when using: decimal(p,s), think of p as how many total digits (regardless of left or right of the decimal point) you want to store, and s as how many of those p digits should be to the right of the decimal point.

DECIMAL(10,5)=     12345.12345
DECIMAL(10,2)=  12345678.12
DECIMAL(10,10)=         .1234567891
DECIMAL(11,10)=        1.1234567891

your sample code fails:

SELECT CAST(123456789 as decimal(9,1))

because:

9=precision (total number of digits to left and right of decimal)
1=scale (total number of digits to the right of the decimal)
(9-1)=8 (total digits to the left of the decimal)

and your value 123456789 requires 9 digits to the left of the decimal. you will need decimal(10,1) or just decimal(9,0)