numeric precision and scale question

  • 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... 🙂

  • 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!

  • 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!

  • 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. 😉

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

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