Practical uses of binary types

The binary datatype of SQL Server is one of those features most developers don’t really use that often, but it turns out there’s more to binary values than just storing large, non-relational blobs.

The binary datatype

First off, how does the binary datatype work? Think of it as a char/varchar string, except it’s a lot simpler, because code pages and collation orders don’t apply. Like the name implies, a binary value in SQL Server is a series of bytes (known as a byte array in some programming languages).

Just like char/varchar, there are fixed-length types, binary(1-8000), and variable-length ones, varbinary(1-8000) and varbinary(max).

Hexadecimal numbers

Management Studio displays binary values as hexadecimal values prefixed with “0x”. You can skip the rest of this section if you’re familiar with how hex values work.

The hexadecimal system works like the decimal system, except it uses base-16 instead of base-10. That means that whereas decimal values can use 0-9 to compose numbers, hex uses 0-9 and A-F, where A-F represent 10-15. All of this is important because one byte contains 8 bits, which allows for 256 discrete integer values, so it’s practical to represent bytes in hexadecimal, because the range 0-256 corresponds to 00-FF in hex.

So multiplying a hex value by 10 (hex) means multiplying it with 16 (decimal).

• 0x01 is 1
• 0x10 is 16
• 0x20 is 32
• 0x0100 is 256
• 0x0200 is 512
• 0x1000 is 4096
• … etc

For clarity, hex values should be prefixed with “0x” so a reader can tell the difference between for instance 0x20 (which is 32 in decimal) and 20 (which is 0x14 in hex). Incidentally, if you prefix a value in T-SQL with 0x, you get an explicit binary value. Try this:

SELECT 0x41;

Converting to and from strings

Because binary values are essentially strings, they easily convert to and from character strings, using CAST or CONVERT. To convert the binary value of 0x41 to a plain-text character value, try

SELECT CAST(0x41 AS char(1));    --- 'A'

The binary value 0x41 is equivalent to decimal 65, and CHAR(65) is the letter “A”. Note that I haven’t placed any quotes around 0x41 – that’s because it’s a numeric value (albeit in hex notation) and not a string.

Converting in the opposite direction also works way you’d expect:

SELECT CAST('A' AS binary(1));   --- 0x41

You can convert not only single bytes but an entire string this way.

SELECT CAST('Hello world!' AS varbinary(max));
                         --- 0x48656C6C6F20776F726C6421

… and back:

SELECT CAST(0x48656C6C6F20776F726C6421 AS varchar(max));
                         --- 'Hello world!'

For these examples, CAST() and CONVERT() produce the same results, I just prefer the CAST() syntax out of habit.

Using CONVERT() with conversion styles

However CONVERT(), as opposed to CAST(), allows you to specify a conversion style, which defines additional formatting rules when converting. This mostly applies to date conversions where you may want to convert a date column to/from a specific format, but it also comes in handy when converting binary values. Consider the following:

DECLARE @s varchar(100)='0x48656C6C6F';
SELECT CONVERT(varbinary(max), @s);     --- 0x307834383635364336433646

Because the value I want to convert is a string value, that string value is literally converted to a binary value, so the leading “0x” becomes 0x3078, and so on. What we really wanted to do was just to change the datatype. To remedy this, we can add a conversion style to the CONVERT():

DECLARE @s varchar(100)='0x48656C6C6F';
SELECT CONVERT(varbinary(max), @s, 1);  --- 0x48656C6C6F

The “, 1” style tells SQL Server that you don’t want to convert the string to its corresponding ASCII codes, but rather treat the string as a hex string in text form. You could specify “, 2” if you wanted to skip the “0x” part as well.

This conversion style option is great when you’re accepting hex values as arguments to a function or stored procedure, provided in a plain-text char/varchar variable.

varbinary vs binary

When you convert a value to a binary (which is fixed-length, just like char), the resulting binary is always padded to its declared length. This works just like char values, except it’s padded with 0x00 instead of spaces. A varbinary isn’t padded.

When adding binary and varbinary values, there’s no mathematical addition going on, instead the two values are appended like strings.

SELECT CAST('H' AS binary(1))+
       CAST('e' AS binary(1))+
       CAST('l' AS binary(1))+
       CAST('l' AS binary(1))+
       CAST('o' AS binary(1));  --- 0x48656C6C6F

Now, look what happens when we change the declared length of each part:

SELECT CAST('H' AS binary(2))+
       CAST('e' AS binary(2))+
       CAST('l' AS binary(2))+
       CAST('l' AS binary(2))+
       CAST('o' AS binary(2));  --- 0x480065006C006C006F00

Notice that each binary(2) is padded with a 0x00. And because we think of binary(2) as a string like char(2), that padding happens on the right of each part.

Converting to and from integer types

Binary values can be converted directly to various numeric types without having to first convert to a string type and using ASCII(), which may be useful in some cases. For one, the ASCII() function is limited to just the first character of any string:

SELECT ASCII('Hello');    --- 72 (the ascii code of 'H')

If you instead, turn “Hello” into a binary string, you can convert the whole string into an bigint (int is four bytes and cannot store the five bytes required in this example)

SELECT      CAST('Hello' AS varbinary(max));            -- 0x48656C6C6F
SELECT CAST(CAST('Hello' AS varbinary(max)) AS bigint); -- 310939249775
/*
H =  72       72*256*256*256*256 = 309237645312
e = 101      101*256*256*256     =   1694498816
l = 108      108*256*256         =      7077888
l = 108      108*256             =        27648
o = 111      111                 =          111
                                 = 310939249775
*/

This is admittedly a slightly contrived example, but you may just run into a practical real-world application some day.

Building a better char2hex function

Years ago, I built a rather cumbersome function to convert a single character to its hex code. It looked something like this:

CREATE FUNCTION dbo.fn_char2hex(@char char(1))
RETURNS char(2)
AS

BEGIN;
    DECLARE @hex char(2), @dec int;
    SET @dec=ASCII(@char);
    SET @hex= --- First hex digit:
             SUBSTRING('0123456789ABCDEF', 1+(@dec-@dec%16)/16, 1)+
              --- Second hex digit:
             SUBSTRING('0123456789ABCDEF', 1+(     @dec%16)   , 1);
    RETURN(@hex);
END;

In retrospect, I could have simplified this function considerably by using a binary conversion like we’ve discussed above:

CREATE FUNCTION dbo.fn_char2hex(@char char(1))
RETURNS char(2)
AS

BEGIN;
    RETURN(CONVERT(char(2), CAST(@char AS binary(1)), 2));
END;

Notice the “, 2” style in the CONVERT() function – it signals that the conversion from binary to char should not be translated to the corresponding ASCII code, and that it should eliminate the “0x” prefix.