SQL Server BINARY(N) Data Type

The BINARY(N) data type is a data type in SQL Server that is used to store fixed-length binary data.

Syntax

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

BINARY [ (n) ]

Here, n specifies the number of bytes to be stored, ranging from 1 to 8000. If n is not specified, it defaults to 1.

Use Cases

The BINARY(N) data type is typically used to store binary data such as images, audio, and video files. It can also be used to store fixed-length data such as encrypted data or checksums. In some cases, the BINARY(N) data type may be more suitable for storing data than the VARCHAR(N) or VARBINARY(N) data types because it can provide better performance and space utilization.

Examples

Here are two examples of using the BINARY(N) data type to create and use this data type in SQL Server.

Example 1

Suppose we have a table containing employee information, including a photo of each employee. We can use the following SQL statement to create the table:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  photo BINARY(5000)
);

In the above SQL statement, we use the BINARY(5000) data type to store employee photos. Now, we can insert some data into the table so we can test whether it works as expected.

We can now use the following query to retrieve the photo of the employee with an ID of 1:

SELECT photo
FROM employees
WHERE id = 1;

After running the above query, we will get a result set containing binary data. We can use an appropriate tool to convert it to an image format.

Example 2

Suppose we have a table that needs to store checksums. We can use the following SQL statement to create the table:

CREATE TABLE checksums (
  id INT PRIMARY KEY,
  data BINARY(32)
);

In the above SQL statement, we use the BINARY(32) data type to store the SHA-256 checksum, which consists of 32 bytes. Now, we can insert some data into the table so we can test whether it works as expected.

We can now use the following query to retrieve the checksum of the data with an ID of 1:

SELECT data
FROM checksums
WHERE id = 1;

After running the above query, we will get a result set containing binary data representing the SHA-256 checksum of the data with an ID of 1.

Conclusion

The BINARY(N) data type is used in SQL Server to store fixed-length binary data. It can provide better performance and space utilization and can be used to store data such as images, audio, video files, encrypted data, or checksums. When using the BINARY(N) data type, it is important to specify the number of bytes to be stored and to ensure that the stored data is the same as the specified number of bytes.