SQL BINARY Data Type

The BINARY data type holds fixed-length binary data.

Use this data type when the data is expected to be consistent in length.

The maximum size for BINARY is 8,000 bytes.

Example

#

A table with a BINARY column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  FullName VARCHAR(100),
  IsRegistered BINARY
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith', 0);  
INSERT INTO DemoTable VALUES ('Robert Johnson', 0);  
INSERT INTO DemoTable VALUES ('Janice Lopez', 1);
INSERT INTO DemoTable VALUES ('Kelly Wilson', 1); 
INSERT INTO DemoTable VALUES ('Grace Taylor', NULL); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Result:  5 records
Id FullName IsRegistered
1 Harold Smith 0x00
2 Robert Johnson 0x00
3 Janice Lopez 0x01
4 Kelly Wilson 0x01
5 Grace Taylor NULL

We can interpret a 0 in IsRegistered as False and 1 as being True.


Syntax

#

Syntax of BINARY.

BINARY(number)

number -- optional, number of bytes (1 - 8,000). Default is 1.


More Examples

BINARY with CONVERT BIT value

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products and whether they are discontinued.
SELECT ProductName, 
       CAST(IsDiscontinued AS BINARY(1)) AS IsDiscontinued
  FROM Product

The boolean value is cast to a fixed sized BINARY(1) type.
This example is for demonstration purposes only as it does not serve a real purpose.

Result:  78 records
ProductName IsDiscontinued
Chai 0x00
Chang 0x00
Aniseed Syrup 0x00
Chef Anton's Cajun Seasoning 0x00
Chef Anton's Gumbo Mix 0x01

You may also like



Guides


vsn 3.1