1

If I have a column of type binary or varbinary, I imagine the data as a sequence of bits. For example, it makes sense to me that 01001 (as a base 2 number) could be a valid value in a binary(5) column.

Is there a reason why inserting and displaying binary data in this fashion is not straightforward?

For example, why does SSMS convert binary data to hexadecimal instead of displaying a sequence of ones and zeroes (which in my opinion is much easier to reason about)?

6
  • 1
    Are you asking a question or complaining about how SQL and other RDMBs treats binary types?
    – McNets
    Dec 2, 2021 at 18:53
  • 1
    @McNets Given how obvious it seems to me that binary data could be displayed how I described, I'm sure there's a core flaw in my understanding and a good reason it isn't done this way. I'm hoping someone can explain it to me and fill in the gaps in my knowledge. Dec 2, 2021 at 18:55
  • Yes, but what DBMS do you know that work the way you expect? Binary types store binary strings. Sql Server, PostgreSQL, MySQL
    – McNets
    Dec 2, 2021 at 19:07
  • @McNets Binary data types in SQL Server do not store strings. They can be represented as strings, but that’s not the same as storage. Dec 2, 2021 at 20:11
  • Well, read the text of the answer from a Ms team member.
    – McNets
    Dec 2, 2021 at 20:16

1 Answer 1

7

Is there a reason why inserting and displaying binary data in this fashion is not straightforward?

Yes. Base-2 binary strings are very long and inefficient, and so almost all programming languages and platforms use Base-16 or "hex strings" for reading and writing binary data in human-readable form.

So instead of

01001101

you use one ascii character 0-9, A-F for each 4 bits, and have

4D

3
  • For my own curiosity, any reason the name binary was used?...is this just legacy naming convention that carried over?
    – J.D.
    Dec 2, 2021 at 20:18
  • 2
    I think because “binary” has always been used as the opposite of “text” or “character” when describing data types and data file layout. Dec 2, 2021 at 20:30
  • 1
    Also because those types are “binary safe” - you won't run into any codepage conversions, or character interpretation issues, or simply some codepoints being rejected as invalid, and other such, that you might in some systems with text data. A lot of services/protocols make a distinction between text/binary for similar reasons (if you send data via FTP when not in binary mode, the high bit may get wiped causing corruption as the standard assumes content to be 7-bit only ASCII text unless told otherwise, same for some mail transports). Dec 10, 2021 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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