Using Binary Data
The binary and varbinary data types store strings of bits. Although character data is interpreted based on the Microsoft SQL Server 2005 code page, binary and varbinary data is simply a stream of bits.
binary data can store a maximum of 8,000 bytes. varbinary, using the max specifier, can store a maximum of 2^31 bytes. For more information about varbinary(max), see Using Large-Value Data Types
Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern. For example, 0x2A specifies the hexadecimal value of 2A, which is equivalent to a decimal value of 42 or a one-byte bit pattern of 00101010.
Use binary data when storing hexadecimal values such as a security identification number (SID), a GUID (using the uniqueidentifier data type), or a complex number that can be stored using hexadecimal shorthand.
The following Transact-SQL example stores a SID and hexadecimal literal:
USE AdventureWorks;
GO
CREATE TABLE MyCcustomerTable
(
user_login varbinary(85) DEFAULT SUSER_SID(),
data_value varbinary(1)
);
GO
INSERT MyCustomerTable (data_value)
VALUES (0x4F);
GO