How To Convert Decimal to Binary in SQL Server when it Requires more Bytes than Datalength

Question

A value of Decimal(4,0) requires 5 bytes to store in SQL Server. This is according to the documentation that says all decimal and numeric types with precision 1-9 take 5 bytes. Also the DATALENGTH function confirms:

select DATALENGTH(convert(Decimal(4,0),256)) result

result
-----------
5

(1 row(s) affected)

However, when I convert to binary(5) and then back to decimal(4,0) it truncates data.

DECLARE @myval decimal (4, 0);  
SET @myval = 257;  
SELECT CONVERT(decimal(4,0), CONVERT(varbinary(5), @myval)) result

result
---------------------------------------
1

(1 row(s) affected)

The last byte of the number is chopped off. However, if I convert to binary(6) or more... I get the correct results:

DECLARE @myval decimal (4, 0);  
SET @myval = 257;  
SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result  

result
---------------------------------------
257

(1 row(s) affected)

What is happening? If I need to store a binary representation of a decimal value, how do I know how many bytes will be needed? Specifically, what is the generic formula to determine smallest number x of bytes required to convert decimal(p,s) to binary(x)?

I need to do some binary data marshaling into a service broker message so I need to convert some data of various types to a binary string. Is there a more robust way to store decimal values in binary than using cast/convert?

Answer 1

Let's start from DATALENGTH. From MSDN:

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

The minimum length of decimal is 5 bytes, maximum - 17 bytes. Decimal(p,s) is NOT variable-length data. It has fixed length according to precision. For example if length of numeric is from 1 to 9 number then DATALENGTH will always return 5

select DATALENGTH(convert(Decimal(38,0), 1)) -- result 5
select DATALENGTH(convert(Decimal(38,0), 1234567890)) -- result 5

if length of numeric is from 10 to 19 number then DATALENGTH will always return 9

select DATALENGTH(convert(Decimal(38,0), 12345678901)) -- result 9
select DATALENGTH(convert(Decimal(38,0), 111111111111111)) -- result 9

so, result of DATALENGTH will depend of numeric's lenght but it is not real lenght.

When you convert decimal(4,0) to binary(5) you will get 0x04 00 00 01 00 In this case, only the last byte is left for your number. The maximum number that you can store in 1 byte is 255 (255 in HEX equals FF)

In that way everything works fine:

DECLARE @myval decimal (4, 0);  
SET @myval = 255;  
SELECT CONVERT(decimal(4,0), CONVERT(binary(5), @myval)) result, CONVERT(binary(5), @myval)

result                                  HEX
--------------------------------------- ------------
255                                     0x04000001FF

Now, try number 256 instead of 255. 256 in HEX equals 100, we can't store 100 in 1 byte (HEX should be 0x04 00 00 01 00 1 but there is not room for 1)

DECLARE @myval decimal (4, 0);  
SET @myval = 256;  
SELECT CONVERT(decimal(4,0), CONVERT(binary(5), @myval)) result, CONVERT(binary(5), @myval) HEX
result                                  HEX
--------------------------------------- ------------
0                                       0x0400000100

If you want to store numbers from 0 to 9999 then you need at least 6 bytes. Look at 257 (in HEX equals 101)

DECLARE @myval decimal (4, 0);  
SET @myval = 257;  
SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result, CONVERT(binary(6), @myval) HEX
result                                  HEX
--------------------------------------- --------------
256                                     0x040000010101

here we have 6 bytes 0x04 00 00 01 01 01 and 01 01 in the end Then 9999 (HEX equals 270F)

DECLARE @myval decimal (4, 0);  
SET @myval = 9999;  
SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result, CONVERT(binary(6), @myval) HEX
result                                  HEX
--------------------------------------- --------------
9999                                    0x040000010F27

6 bytes 0x04 00 00 01 0F 27 and 27 0F in the end. (read from right to left)