binary and varbinary (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Binary data types of either fixed length or variable length.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
Use binary when the sizes of the column data entries are consistent.
Use varbinary when the sizes of the column data entries vary considerably.
Use varbinary(max) when the column data entries exceed 8,000 bytes.
When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.
Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move data around. Converting any value of any type to a binary value of large enough size and then back to the type, will always result in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.
You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:
SELECT CAST( 123456 AS BINARY(4) );
However, the following SELECT statement shows that if the binary target is too small to hold the entire value, the leading digits are silently truncated so that the same number is stored as 0xe240:
SELECT CAST( 123456 AS BINARY(2) );
The following batch shows that this silent truncation can affect arithmetic operations without raising an error:
DECLARE @BinaryVariable2 BINARY(2); SET @BinaryVariable2 = 123456; SET @BinaryVariable2 = @BinaryVariable2 + 1; SELECT CAST( @BinaryVariable2 AS INT); GO
The final result is 57921, not 123457.
Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.