Converting binary and varbinary Data

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, Microsoft® 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 done with hexadecimal zeros.

The conversion of money, datetime, smalldatetime, and numeric data types to or from binary or varbinary are different from earlier versions of SQL Server because of a change in storage representation.

You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, it will be different from the original integer value if truncation has occurred. For example, this SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:

SELECT CAST( 123456 AS BINARY(4) )

This 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) )

This batch shows that the 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.

Note Conversions between any data type and the binary data types are not guaranteed to be the same between SQL Server versions.

See Also