6

I have a requirement in which I want to store a byte array in a sql server table column of type Varbinary using C# code. Now when I try to do this, I get a value in sql server table as "0x4A6974656E6472612053616E6B686C61" for a byte array a[] = { 74, 105, 116, 101, 110, 100, 114, 97, 32, 83, 97, 110, 107, 104, 108, 97}. I am not sure how this is done. The value I get is actually a hexa representation of each decimal number.

Is it like sql server performs some internal conversion while storing a byte array into a varbinary sql table column ?

Improve this question
2
  • Do you want to know how it is being stored in db or want to get back the actual byte array? Commented Mar 29, 2017 at 5:22
  • Please go through this link that represents how VARBINARY(MAX) data type behaves in terms of storing and retriving. As far as I know, The binary representation of a value might be change from version to version of SQL Server. This link will help you converting binary and varbinary Data Commented Mar 29, 2017 at 6:01

2 Answers 2

Reset to default
6

You see a hex-string representation of the actual binary value stored in the field, because that is one semi-readable version to represent the bytes. How else should it be shown?

The string representation and the actual bytes are not the same. This is true for most types. If you store an integer, say 7622389, that too is actually stored as some amount of bytes (depending on the size of the field). But showing the actual bytes instead of the string representation of the value would just be confusing.

For general purpose binary data, the hex-string format is the logical string representation. Sql Server management studio could theoretically show a thumbnail of a picture if the bytes happened to make up an image file or some other well known file format. But since the field just contains general purpose binary data, it cannot really know what would be the best display format so it uses hex-string.

When you actually select the field from the database to your C# program, what you get back will generally be a byte[], sometimes wrapped up inside a container type of some sort.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Try this:

DECLARE @bin VARBINARY(MAX)=0x4A6974656E6472612053616E6B686C61;
SELECT CAST(@bin AS VARCHAR(MAX))

The result Jitendra Sankhla looks pretty - uhm - fitting... :-)

Some background:

VARBINARY is nothing more than a BLOB. In a SELECT it is presented as HEX-string, but this is not the way it is stored actually.

You can store literally everything in a VARBINARYcolumn: pictures, strings, numbers, XML, complex structures... The only - but very important! - point is, that you must know, how to read and interpret this.

Why did I cast this to VARCHAR? Your numbers looked like ASCII-codes for plain letters. VARCHAR is a datatype representing 1-byte-extended-ASCII strings (the bound collation defines - like a codepage - the intpretation and sorting of non-plain characters).
Assuming, that you pass in byte after byte, the conversion to a normal string leads to letters according to the byte's values.

It is obvious, that you won't convert any binary to a string.
Many values between 0 and 255 are not printable. Other sources of pain can be differing collation/codepage settings. Furthermore, if you pass in a string with an encoding like utf-8 you will get some erronous characters, because utf-8 will encode some characters with more than one byte.

But in your simple example it works.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.