numeric precision and scale question

Question

numeric precision and scale question

TB-462197

SSC Eights!

Points: 990
More actions
September 27, 2011 at 6:41 am

#248777

I am embarrassed to ask this question; I've read so many things on precision and scale but still do not understand why the following happens:
----------------
DECLARE @numerator numeric(8,3)
DECLARE @denominator numeric(8,3)
SET @numerator = 3
SET @denominator = 14
select @numerator / @denominator
result: 0.214285714285
---------------
If your precision is 8 places and your scale is 3 places, wouldn't that result in 0.214? Or... 0.21428571?
Why is the actual result 12 decimal places?
Again, I apologize - I have been trying to find a link that explains at a second grade level... 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

when you do math on numerics and decimals, SQL server is free to use the maximum precision unless you declare it ootherwise.

DECLARE @numerator numeric(8,3)

DECLARE @denominator numeric(8,3)

SET @numerator = 3

SET @denominator = 14

select convert(numeric(8,3),@numerator / @denominator)

precision is the maximum number of significant digits.

in your example, [0.214285714285] has the minimum precision of 13 (it's 14 chars including the period, right?)

scale is how many of those digits are to the right of the decimal point.

in your example, [0.214285714285] has the minimum scale of 12

so it will fit in a decimal(13,12) or anything with a larger prcision...decimal(38,12) for example.

problems occur, or implicit conversions occur, when the value you are using is larger than those limits.

the money datatype is actually a decimal(19,4) for example.

a decimal has a maximmum precision of 38.

try playing with this simple example, changing the precisiona dn scale to get converisons and errors so you understand the concept

declare @value decimal(38,20)

SET @value = 100.214285714285

select convert(decimal(10,2),@value)--100.21

select convert(decimal(10,7),@value)--100.2142857

select convert(decimal(10,10),@value)--error Msg 8115, Level 16, State 8 Arithmetic overflow error converting numeric to data type numeric.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TB-462197 SSC Eights! Points: 990 More actions · Answer 2

aha! That's interesting. I was assuming SQL would impose some sort of significant digits based on the inputs. I guess that's a lot to expect as different people have different needs and expectations.

Speaking of expectations and based on your answer, perhaps I should just tell it what I want ahead of time:

DECLARE @numerator numeric(8,3)

DECLARE @denominator numeric(8,3)

DECLARE @result numeric(8,3)

SET @numerator = 3

SET @denominator = 14

SET @result = @numerator / @denominator

SELECT @result

Thanks for clearing up the mystery!

TB-462197 SSC Eights! Points: 990 More actions · Answer 3

also, thank you very much for the select convert(decimal(10,10),@value) example - it took me a minute to sort out why that threw the error. 😉