What is the cause of this problem with CONVERT()? Consider the following two statements:

PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0);
PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0);


Both statements return -1; isn't that incorrect since the second binary value is decimal 65,536 higher than the first value, is it not?

Surely this cannot be due to silent truncation?

If I run the following statements:

PRINT CONVERT(NUMERIC(38, 0),   0x00000001, 0);
PRINT CONVERT(NUMERIC(38, 0),   0x00010001, 0);


I am presented with the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varbinary to numeric.


How can I diagnose what is happening here?

I'm running this on SQL Server 2012, v11.0.5058.  The results are the same on SQL Server 2008 R2 SP2, SQL Server 2005, and SQL Server 2000.
Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try:

SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0);


As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.