SQL Server 2008 - Having trouble understanding decimal

Question

I need to insert numbers with decimals into a SQL Server 2008 database. It seems like decimal() is the correct data type to use, however, I'm having trouble understanding it exactly.

I found this script (scroll down for decimal):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95322

Which allows me to test different decimal settings against numbers, and there's some I don't understand why they pass or fail. The way I understand it, when using decimal(precision, scale), precision is the number of digits to the left of the decimal and scale is the number of digits to the right of the decimal. Using this function, I don't understand why some are passing and why some are failing.

SELECT dbo.udfIsValidDECIMAL('2511.1', 6, 3)    

I have 4 digits on the left and 1 on the right, yet this fails.

SELECT dbo.udfIsValidDECIMAL('10.123456789123456789', 18, 17)    
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789', 18, 16)

The first one fails, the second one passes. There are 18 digits after the decimal point, so it seems like both should fail (or pass and SQL truncates the number).

Maybe I have a fundamental misunderstanding in how decimal() is supposed to work?

Answer 1

DECIMAL(6,3) means: 6 digits in all, 3 of which to the right of the decimal point.

So you have 3 digits before, 3 digits after the decimal point, and of course it cannot handle 2511.1 - that's got four digits to the left of the decimal point. You'd need DECIMAL(7,3) to handle that.

See the MSDN documentation on DECIMAL:

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]

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.

s (scale)

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.

Answer 2

cast(10.123456789123456789 as decimal(18,17))

A precision of 18 & scale of 17 allows just 1 digit to the left of the decimal place, but there are 2 in that example.

cast(10.123456789123456789 as decimal(18,16)

Has room for 2 digits so succeeds.

Answer 3

Precision is the number of digits that can be stored total.

So the number to the left of the decimal will be precision - scale.

For example, your first example will fail because you are only allowing for three places to the left of the decimal:

SELECT dbo.udfIsValidDECIMAL('2511.1',6,3)