[FIXED] How SQL Server store/transfer decimal stream

Issue

I'm working on MS-TDS protocol, what I'm trying todo is to read/write TDS stream, and get/set a System.Data.DataTable from/to it. I got all C# types working already, except decimal varchar(max) varbinary(max) columns

in C#, I got following results for 12345678912345678912.3456789m and 1234567891234567891.23456789m

var bits = decimal.GetBits(12345678912345678912.3456789m)
using (var ms = new System.IO.MemoryStream())
{
    foreach (var b in bits)
        ms.Write(BitConverter.GetBytes(b), 0, 4);
    var decimalBytes = ms.ToArray();
}

 10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 07 00
 10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 08 00

these totally make sense to me, the last byte is decimal size(position), which is 7 and 8, the value part is the same. but in sql, they look completely different:

SELECT CAST(CAST(12345678912345678912.3456789 AS decimal(38,15)) AS varbinary)
SELECT CAST(CAST(1234567891234567891.23456789 AS decimal(38,15)) AS varbinary)
results:
0x260F 0001 0075 AA3F 0AF2 2A3A DB18 0560 B060 0200
0x260F 0001 80D8 5D06 01E5 9D52 7C82 0070 DE3C 0000

and in TDS stream DataType=[0x6A,0x11,0x26,0x0F] decimal(38,15), sql server returns this:

0x81, // token "COLMETADATA"
0x03, 0x00, 0x00, 0x00, // 3 columns

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa7, 0xff, 0xff, 0x09, 0x04, 0xd0, 0x00, 0x34, // 0xA7=BIGVARCHRTYPE  0xffff=MAX, 
0x0e, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x43, 0x00, 0x48, 0x00, 0x41, 0x00, 0x52, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARCHARMAX_Col"

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0x6a, 0x11, 0x26, 0x0f, // 0x6A=DECIMAL  0x11=Size, precision = (38,15)
0x0b, 0x44, 0x00, 0x45, 0x00, 0x43, 0x00, 0x49, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x4c, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "DECIMAL_Col"

0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa5, 0xff, 0xff, // 0xA5=BIGVARBINTYPE 0xffff=MAX
0x0d, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x42, 0x00, 0x49, 0x00, 0x4e, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARBINMAX_Col"

0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x42, 0x00, 0x00, 0x00, 0x00, // varchar(max) value, how to read this ?
0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00, // decimal value, how to read this ?
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x62, 0x00, 0x00, 0x00, 0x00, // varbinary(max) value, how to read this ?

0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x43, 0x00, 0x00, 0x00, 0x00,
0x11, 0x01, 0x00, 0x75, 0xaa, 0x3f, 0xa6, 0x63, 0x9d, 0x02, 0x1b, 0x91, 0x57, 0xa1, 0xa6, 0x73, 0x00, 0x00,
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x63, 0x00, 0x00, 0x00, 0x00,

0xd2,  // token "NBCROW"
0x05,  // NullBitmap 00000101:  1st and 3rd columns are null
0x11, 0x01, 0x50, 0x24, 0xc2, 0x2a, 0xf2, 0x77, 0xd5, 0x97, 0x0f, 0x03, 0xee, 0xf5, 0x02, 0x00, 0x00, 0x00,

decimal value in the 1st row is

0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00, 

0x11 should be length, 0x01 means positive value (from this post How does SQL Server store decimal type values internally?) Question: how can I resolving these bytes to a decimal value? and how should I write varchar(max) varbinary(max) value to TDS ROW TokenStream? Any help is appreciated

Solution

I figured it out. the difference is TDS stream use the first byte as sign and use fixed Decimal point, decimal.GetBits use few bits in last byte as sign and Decimal point

// write decimal to TDS stream
static byte[] DecimalBytes(decimal dec, int precision = 15)
{
    var round = decimal.Round(dec, precision);
    var valueToWrite = round;
    var sign = round < 0 ? (byte)0x00 : (byte)0x01;
    // get the string
    var str = round.ToString();
    // string without Decimal point
    var numbers = str.Replace(".", string.Empty);
    var dotIdx = str.IndexOf('.');
    if (dotIdx > 0)
    {
        // there must be {precision} digits on the right side of "."
        var padding = precision - (numbers.Length - dotIdx);
        // padding numbers with '0' to precision length
        numbers = numbers.PadRight(numbers.Length + padding, '0');
    }
    else
    {
        numbers = numbers.PadRight(numbers.Length + precision, '0');
    }
    if (!decimal.TryParse(numbers, out valueToWrite))
    {
        throw new ArgumentOutOfRangeException($"Invalid decimal value for Database Type decimal(38,{precision})");
    }
    using (var ms = new System.IO.MemoryStream())
    {
        var bits = decimal.GetBits(valueToWrite);
        ms.WriteByte(0x0D); // length=13
        ms.WriteByte(sign);
        ms.Write(BitConverter.GetBytes(bits[0]), 0, 4);
        ms.Write(BitConverter.GetBytes(bits[1]), 0, 4);
        ms.Write(BitConverter.GetBytes(bits[2]), 0, 4);
        return ms.ToArray();
    }
}

Answered By - Leowan

Answer Checked By - Marilyn (PHPFixing Volunteer)