SQL VARBINARY Data Type

The VARBINARY data type holds variable-length binary data.

Use this type when the data is expected to vary in size.

The maximum size for VARBINARY is 8,000 bytes.

As an aside, the word VARBINARY stands for varying binary.

Example

#

A table with a VARBINARY column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  FullName VARCHAR(100),
  IsRegistered VARBINARY
);
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

Note: The default size of VARBINARY is 1 byte.

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

In this example, a 0 value of IsRegistered is considered False, and 1 is True.


Syntax

#

Syntax of VARBINARY.

VARBINARY(number)

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


More Examples

CAST BIT value to VARBINARY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List products and whether they are discontinued.
SELECT ProductName, 
       CAST(IsDiscontinued AS VARBINARY(1)) AS 'IsDiscontinued'
  FROM Product

This demonstrates a cast to a fixed sized VARBINARY(1) type.

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