12

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.

  • 4
    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); – Aaron Bertrand Dec 29 '14 at 21:07
  • 4
    Aaron is spot on. Your brain is converting the binary data to integer data then straight to numeric, but SQL Server doesn't do that implicit conversion from binary -> integer -> numeric(x, y). For SQL Server to follow your thought process, you'd have to do something like this: PRINT CONVERT(NUMERIC(38, 0), convert(int, 0x00000001), 0); PRINT CONVERT(NUMERIC(38, 0), convert(int, 0x00010001), 0);. – Thomas Stringer Dec 29 '14 at 21:11
  • 5
    The first byte is scale (0x01 = 1), The second byte is precision (0x00 = 0), The final byte is the value (0x01 = 1). Not sure what bytes three and four are for. The sign is in there but that doesn't need two bytes. Certainly flipping that bit doesn't seem to have affected anything. – Martin Smith Dec 30 '14 at 9:35
  • 1
    Thanks, @MartinSmith - how on earth did you determine the first two bytes are used like that? Is that documented? – Max Vernon Dec 30 '14 at 15:17
  • 3
    @AaronBertrand: Would you like to make that an answer? We can mark this off the "unanswered" list. – Jon of All Trades Jan 9 '15 at 22:56
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.