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.

I am attempting to determine if you could use a BINARY(5) column as a numeric primary key value of 40 bits.

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

In order to return the correct result, you'd need to convert the varbinary value to an int, first, as in:

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

The above print statements return 1 and 65537 respectively.

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

StackExchange Database Administrators Q#87199, answer score: 2