How a byte array is being stored in sql server table column of type Varbinary?

Question

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 ?

Answer 1

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.

Answer 2

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.