Binary, Varbinary & Varbinary(max) are the binary string data types in SQL Server. These data types are used to store raw binary data up to a length of (32K – 1) bytes. The contents of image files (BMP, TIFF, GIF, or JPEG format files), word files, text files, etc. are examples of binary data.
Table of Contents
Binary
Binary is a fixed width data type
The syntax for declaring Binary variable is binary(n), where n defines the size in bytes. Note that size is in bytes and not number of characters.
The Binary data type always uses the n bytes of storage irrespective of the size of the data
For Example, when we declare as binary(10), The column will occupy 10 bytes of storage. The value of n can be from 1 to 8000 bytes.
Varbinary
VarBinary is a variable width data type.
The syntax for declaring Binary variable is varbinary(n), where n defines the maximum size in bytes.
The varbinary data type uses actual length of the data entered + 2 bytes as the storage.
Varbinary(max)
Similar to varbinary(n), the max argument is used when the length of the binary data is expected to exceed the 8000 bytes.
IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Binary Data Type Examples
Creating Tables with Binary Columns
1 2 3 4 5 6 7 8 | CREATE TABLE testBinary ( colBinary BINARY(5) , colBinaryMax VARBINARY(50) , colVarBinay VARBINARY(Max) ); |
Inserting Values into Binary Columns
1 2 3 4 5 6 7 8 9 10 11 12 13 | INSERT INTO testBinary (colBinary, colVarBinary,colVarBinaryMax ) VALUES (0x12345, 0x12345,0x12345); SELECT * FROM testBinary; colBinary colBinary colBinaryMax ------------ --------- ------------ 0x0123450000 0x012345 0x012345 |
Using Binary Local Variables
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @bin VARBINARY(MAX) DECLARE @str varchar(MAX) SET @str = 'Hello World' SELECT @str //Hello World SET @bin = CAST(@str AS VARBINARY(MAX)) SELECT @bin //0x48656C6C6F20576F726C64 SET @str = CAST(@bin AS varchar(MAX)) SELECT @str //Hello World |
Converting to and from binary
Conversions between any data type and the binary data types are not guaranteed to be the same between different versions of SQL Server.
Binary to String
Conversion from binary to string is implicit. In the following example, we assign binary value @b to string without using any conversion functions.
1 2 3 4 5 6 7 8 9 10 | declare @b varbinary(max) declare @str varchar(max) set @b = 0x48656C6C6F20576F726C64 set @str=@b select @str ----------- Hello World |
String to Binary
But SQL Server does not do a implicit conversion from string to binary. The following code results in error Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
1 2 3 4 5 6 7 | DECLARE @bin VARBINARY(MAX)='Hello World' Select @bin ------------------------------------ Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. |
We need to use the cast or convert function and explicitly convert the string to the number
1 2 3 4 | DECLARE @bin VARBINARY(MAX)=CAST('Hello World' AS VARBINARY(MAX)) Select @bin |
When we convert string data to binary of larger length, SQL Server pads the data with zero on the right.
1 2 3 4 5 6 7 8 | declare @b binary(10) set @b = CAST( 'Hello' AS VARBINARY(MAX)) select @b ---------------------- 0x48656C6C6F0000000000 //zero is padded on the Right |
When we convert string data to binary of smaller length, SQL Server truncates the data on the right..
1 2 3 4 5 6 7 8 | declare @b binary(5) set @b = CAST( 'Hello World' AS VARBINARY(MAX)) select CAST(@b AS varchar(MAX)) ------- Hello //World is truncated |
Binary to numeric
When you convert the number to binary, the leading digits are silently truncated
For Example, in the following code @bnum variable can only store 2 bytes, which is insufficient to store the integer 123456. No errors are throw here, but the leading digits of the number is truncated. When we reconvert the results back, we will get an entirely different result.
1 2 3 4 5 6 7 8 | Declare @num as int=123456 declare @bnum as binary(2)=@num select cast(@bnum as int) ----- 57920 |
Increase the size of the @bnum to binary(3) to get the correct result
1 2 3 4 5 6 7 8 9 10 | Declare @num as int=123456 declare @bnum as binary(3)=@num select cast(@bnum as int) ------ 123456 |
Binary to Binary
Converting from One Binary data type to another binary data type, will also result in truncation if the target type have insufficient space
1 2 3 4 5 6 7 | Declare @bin as binary(3) = 0x48656C6C6F20576F726C64 SELECT cast (@bin as varchar(20)) ----- Hel |
Ensure that the target type have sufficient bytes
1 2 3 4 5 6 7 | Declare @bin as binary(20) = 0x48656C6C6F20576F726C64 SELECT cast (@bin as varchar(20)) ----------- Hello World |
Which one to choose
You need to choose the right binary data type. This is because of the silent padding & truncation that happens, when we convert data from another type to binary and vice versa.
| Data type | Use when … |
|---|---|
| binary | the sizes of the column data entries are consistent. |
| varbinary | the sizes of the column data entries vary considerably. |
| varbinary(max) | the column data entries exceed 8,000 bytes. |
I am still not convinced with how when we reconvert the binary to number back, we will get an entirely different result.