SQL Server VARBINARY(N) Data Type

The VARBINARY(N) data type is used to store variable-length binary data in SQL Server. In SQL Server, VARBINARY(N) can store binary data ranging from 0 to 8,000 bytes. If you need to store larger binary data, you should use the VARBINARY(MAX) data type.

Syntax

The syntax for the VARBINARY(N) data type is as follows:

VARBINARY(N)

Where N represents the maximum number of bytes to store.

Use Cases

The VARBINARY(N) data type is commonly used to store binary files such as images, audio, video, etc. It can also be used to store encrypted data and hash values. Unlike the BLOB data type, the VARBINARY(N) data type allows you to specify the maximum number of bytes to store, which helps avoid wasted storage space.

Examples

Here are two examples of using the VARBINARY(N) data type.

Example 1: Storing an Image

Assuming you have a table named Image with two columns: ID and ImageData. The ID column is of integer type, and the ImageData column is of type VARBINARY(8000). Here is an example of inserting image data into the Image table:

INSERT INTO Image (ID, ImageData)
VALUES (1, 0xFFD8FFE000104A46494600010101006000600000FFE10016457869660000);

The above statement inserts a JPEG image into the ImageData column.

Example 2: Storing Encrypted Data

Assuming you have a table named Customers with three columns: CustomerID, Name, and CreditCardInfo. The CustomerID and Name columns are of string type, and the CreditCardInfo column is of type VARBINARY(100). Here is an example of inserting encrypted credit card information into the Customers table:

DECLARE @EncryptKey varbinary(100)
SET @EncryptKey = 0x0123456789ABCDEF0123456789ABCDEF
INSERT INTO Customers (CustomerID, Name, CreditCardInfo)
VALUES ('C001', 'John Smith', EncryptByKey(Key_GUID('MyKey'), '1234567890123456', 1, @EncryptKey));

The above statement uses the SQL Server encryption function EncryptByKey to encrypt the credit card information and then inserts the encrypted data into the CreditCardInfo column.

Conclusion

The VARBINARY(N) data type is used to store variable-length binary data in SQL Server. It is commonly used to store binary files, encrypted data, and hash values. By specifying the maximum number of bytes, you can avoid wasted storage space.