SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

PackPrecScale CLR function

By Claude Martel,

Those who are experienced working with the DECIMAL data type know that SQL Server performs «preemptive» truncation prior to performing some math calculation. This is illustrated by the following example. Whether the constant 1.0 is declared as a DECIMAL(38, 18) or a DECIMAL(1, 0) has a huge impact on the result.

DECLARE @D1 DECIMAL(38, 18) = 1.123456789012345678
DECLARE @D2 DECIMAL(38, 18) = 1.0
DECLARE @D3 DECIMAL(1, 0) = 1.0
SELECT @D1 * @D2, @D1 * @D3

Using the CLR gives us more control over decimal math, but the SqlDecimal class is lacking a method that would «pack» a decimal to the smallest possible precision and scale prior to performing calculations. Examples of such methods can be found on various forums, but they rely on the SqlDecimal.ToString() method, which is not culture-independant. The proposed solution relies on using the DIV and MOD operators on the Data property of a SqlDecimal. It is not only culture-independant, it is also nearly twice as fast.

The whole concept behind the algorithm is to catch trailing zeros by alternatively perform a MOD 10 and a DIV 10 until the result is equal to 0. Using x1, x2, and x3, allows to replace operations on 128 bit integers with a sum of operations on 32 bit integers. The same algorithm could be used to write out own culture-independant SqlDecimal.ToString() method.

static readonly byte[] x3 = new byte[] { 7, 9, 2, 2, 8, 1, 6, 2, 5, 1, 4, 2, 6, 4, 3, 3, 7, 5, 9, 3, 5, 4, 3, 9, 5, 0, 3, 3, 6 }; //2^96 expressed in base 10
static readonly byte[] x2 = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 8, 4, 4, 6, 7, 4, 4, 0, 7, 3, 7, 0, 9, 5, 5, 1, 6, 1, 6 }; //2^64 expressed in base 10
static readonly byte[] x1 = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 2, 9, 4, 9, 6, 7, 2, 9, 6 }; //2^32 expressed in base 10


private static SqlDecimal PackPrecScale(SqlDecimal d)
{
    if (d.IsNull) return d;
    if (SqlDecimal.Equals(d, 0)) return SqlDecimal.ConvertToPrecScale(d, 1, 0);

    var d0 = (ulong)(uint)d.Data[0];
    var d1 = (ulong)(uint)d.Data[1];
    var d2 = (ulong)(uint)d.Data[2];
    var d3 = (ulong)(uint)d.Data[3];

    var precision = 0;
    var trailing = 0;
    var endoftrail = false;

    if (d3 != 0)
    {
        precision = 29;
        for (var idx = 28; idx >= 0; idx--)
        {
            var u = x3[idx] * d3 + x2[idx] * d2 + x1[idx] * d1 + d0;
            if (u % 10 != 0) endoftrail = true;
            else if (!endoftrail && trailing < d.Scale) trailing++;
            d0 = u / 10;
        }
    }
    else if (d2 != 0)
    {
        precision = 20;
        for (var idx = 28; idx >= 9; idx--)
        {
            var u = x2[idx] * d2 + x1[idx] * d1 + d0;
            if (u % 10 != 0) endoftrail = true;
            else if (!endoftrail && trailing < d.Scale) trailing++;
            d0 = u / 10;
        }
    }
    else if (d1 != 0)
    {
        precision = 10;
        for (var idx = 28; idx >= 19; idx--)
        {
            var u = x1[idx] * d1 + d0;
            if (u % 10 != 0) endoftrail = true;
            else if (!endoftrail && trailing < d.Scale) trailing++;
            d0 = u / 10;
        }
    }
    while (d0 > 0)
    {
        precision++;
        if (d0 % 10 != 0) endoftrail = true;
        else if (!endoftrail && trailing < d.Scale) trailing++;
        d0 = d0 / 10;
    }

    while (precision <= d.Scale) precision++;
    return SqlDecimal.ConvertToPrecScale(d, precision - trailing, d.Scale - trailing);
}

 
Total article views: 397 | Views in the last 30 days: 397
 
Related Articles
SCRIPT

Script to calculate Precision & Scale of a Decimal Value

A script to help you find the Precision & Scale parts of a decimal value.

FORUM

Issue with a Decimal column in a table

Limitation with Decimal column precision and scale

FORUM

Displaying Fewer Decimal Places

Displaying fewer decimal places

SCRIPT

datetime precision

Accuracy and precision go hand-in-hand. This script helps trim date values to whatever size is store...

FORUM

Remove Decimals Without Rounding

How do I remove decimals

Tags
clr    
 
Contribute