"Reduce" sql decimal data type to least precision and scale based on value

Question

I’m working on a problem involving many sql decimal multiplications using a fairly high precision.

A recent bug prompted me to do some research, and I've come across a humbling article (http://msdn.microsoft.com/en-us/library/ms190476.aspx) on how the precision and scale of the decimal result are calculated.

It’s my understanding that in order to avoid truncation or unexpected rounding, it’s sometimes necessary to cast the operands and possibly the intermediate calculations to sizes that will work and give the desired results. Each operand is given to me as a decimal(19,5). There’s nothing I can do about this since we support such a wide range of values.

My question: Can I reduce/cast the type of these decimal(19,5) values 'on-the-fly' to the smallest decimal type (precision and scale) that will hold the value?

For example, can I cast this variable (@Cw) to a decimal(4,1) 'on-the-fly' before it will get used in my multiplication?

DECLARE @Cw decimal(19,5)
SET @Cw = 113.5

--Some type of CAST, i.e.
--CAST(@Cw as DECIMAL(GetActualPrecision(@Cw), GetActualScale(@Cw)))

-–should show 4 instead of 19
SELECT SQL_VARIANT_PROPERTY(@NewCw, 'Precision') 

–-should show 1 instead of 5
SELECT SQL_VARIANT_PROPERTY(@NewCw, 'Scale')

Thanks!

score 3 · Accepted Answer · answered Jun 15 '11 at 12:20

3

No

There is no dynamic CAST: scale and precision are static: "fixed point"
If you want arbritrary precision use float which is of course "floating point"

We use decimal mostly and don't have any issues.

If you have complex maths that doesn't work too well then I'd consider using CLR in SQL or doing it in client code which has better libraries

answered Jun 15 '11 at 12:20

gbn

422,506
82
585
676

Thanks for the input. I was afraid of that. I was experimenting with the SQL_VARIANT type, but wasn't having much luck. CLR may be an option, and it would probably be more solid than some clever work-around (if there was one). – John Russell Jun 15 '11 at 12:50
At the heart of the problem, when the multiplication causes the precision and scale to exceed a legal size, it seems to default to something like decimal(38,6). Can this default scale be changed? – John Russell Jun 15 '11 at 12:55
No, precision 38 is the maximum. What happens is that when p>38, both p and s are reduced by the same amount to get p = 38. eg http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052 and http://stackoverflow.com/questions/4452747/dont-understand-rounding-behavior-in-sql-server-when-using-division-operator/4453180#4453180 – gbn Jun 15 '11 at 12:57
Back on this issue. We're unable to use CLR on our customer's database. The have tight security, and they're not working with us here. Switching over to floating point arithmetic introduced several errors for numbers that can't be represented. I'm at a loss on what other options I have. I don't know much about user defined types. Would this give us anything? Can the default decimal(38,6) that's used in overflow scenarios be changed somehow? Is there a better way to manage/minimize the precision and scale of our operands? They all come from the same table and they are defined as decimal(19,5). – John Russell Jul 20 '11 at 16:31
I'd suggest trying (38,10) or such throughout and see what happens... I really can't help you sorry: you are hitting the maths limits of SQL Server... – gbn Jul 20 '11 at 16:33

score 0 · Answer 2 · answered Jul 03 '18 at 18:05

I had the same issue and I solved it using CLR. The following C# function "packs" the decimal type to the smallest possible precision and scale.

internal static SqlDecimal PackPrecScale(SqlDecimal d)
{
    //Remove leading zeros
    var s = d.ToString();
    var l = s.Length;
    var indexofperiod = s.IndexOf('.');

    //Remove trailing zeros
    if (indexofperiod != -1)
    {
        while (s[l - 1] == '0') l--;
        if (s[l - 1] == '.')
        {
            l--;
            indexofperiod = -1;
        }
    }

    var precision = 6;
    var scale = 0;
    if (l > 0)
    {
        precision = l;
        if (s[0] == '-') precision--;
        if (indexofperiod != -1)
        {
            precision--;
            scale = l - indexofperiod - 1;
        }
        if (precision < 6) precision = 6;
    }

    return SqlDecimal.ConvertToPrecScale(d, precision, scale);
}

"Reduce" sql decimal data type to least precision and scale based on value

2 Answers2