SQLTeam.com | Weblogs | Forums

Using varchar or varbin for long hex strings

Hi folks!

I have some very long hex strings that I need to store. Actually, I store them in a varchar(max) field. I tried redefining the field to varbin(max) and it works as well. My question is: which method is optimal in terms of space? My thought would be that, given that the strings are not Unicode, 1 byte per character is used in varchar. Therefore, I would think that both methods use the same amount of storage. Can anyone chime in and confirm whether I might be neglecting something in my reasoning? Thanks!

Store them as varbinary, and make sure to CONVERT a hex string correctly:

SELECT CONVERT(varbinary(max),'0x00AA',1) -- the 1 is a conversion style specific to binary

More details on the CONVERT function:

Thanks Robert. I do actually use CONVERT and should have been specific about that in the original post. Thanks for making the point. But my inquiry is really about storage space and whether a hex string stored as char() requires the same amount of space as when it is stored as binary().

Binary will be smaller, you can confirm using the DATALENGTH() function. A char/varchar hex string is storing 2 hexadecimal characters in 2 bytes, whereas binary will use 1 byte.

SELECT DATALENGTH(0xAABB), DATALENGTH('AABB')
1 Like

Ahhhhh, interesting! Thanks !!!!!!