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);
}