SQL VARBINARY(Max) Data Type

The VARBINARY(Max) data type holds large, variable-length binary data.

Use this type when the data is expected to be very large and vary in length.

The maximum size for VARBINARY(Max) is 2GB.

The word VARBINARY stands for varying binary.

Example

#

A table with a VARBINARY(Max) column.

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

In this example, a 0 value of IsRegistered is considered False, and 1 is True.
A more 'real-world' example would be to store very large binary data in this field.


Syntax

#

Syntax of VARBINARY(Max).

VARBINARY(Max)

This type can hold a maximum of 231-1 bytes.


More Examples

CAST BIT value to VARBINARY(Max)

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

This demonstrates a cast to a VARBINARY(Max) 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