Binary data types

Description

For storing raw binary data, such as pictures, in a hexadecimal-like notation, up to a length of (32K – 1) bytes. The UNIQUEIDENTIFIER data type is used for storage of UUID (also known as GUID) values.

Syntax

BINARY [ ( length ) ]
VARBINARY [ ( max-length ) ]
UNIQUEIDENTIFIER

Usage

Binary data begins with the characters “0x” or “0X” and can include any combination of digits and the uppercase and lowercase letters A through F. You can specify the column length in bytes, or use the default length of 1 byte. Each byte stores 2 hexadecimal digits. Even though the default length is 1 byte, Sybase recommends that you always specify an even number of characters for BINARY and VARBINARY column length. If you enter a value longer than the specified column length, Sybase IQ truncates the entry to the specified length without warning or error.

BINARY Binary data of length length bytes. If length is omitted, the default is 1 byte. The maximum size allowed is 255 bytes. Use the fixed-length binary type BINARY for data in which all entries are expected to be approximately equal in length. Because entries in BINARY columns are zero-padded to the column length length, they might require more storage space than entries in VARBINARY columns.

VARBINARY Binary data up to a length of max-length bytes. If max-length is omitted, the default is 1 byte. The maximum size allowed is (32K – 1) bytes. Use the variable-length binary type VARBINARY for data that is expected to vary greatly in length.


Notes

As a separately licensed option, Sybase IQ supports binary large object (BLOB) data with a length ranging from zero (0) to 512TB (terabytes) for an IQ page size of 128KB or 2PB (petabytes) for an IQ page size of 512KB. The maximum length is equal to 4GB multiplied by the database page size. See Large Objects Management in Sybase IQ.

For information on LONG BINARY and IMAGE data types, see “Binary data types”.

Treatment of trailing zeros

All BINARY columns are padded with zeros to the full width of the column. Trailing zeros are truncated in all VARBINARY columns.

The following example creates a table with all four variations of BINARY and VARBINARY data types defined with NULL and NOT NULL. The same data is inserted in all four columns and is padded or truncated according to the data type of the column.

CREATE TABLE zeros (bnot BINARY(5) NOT NULL,
        bnull BINARY(5) NULL,
        vbnot VARBINARY(5) NOT NULL,
        vbnull VARBINARY(5) NULL);
INSERT zeros VALUES (0x12345000, 0x12345000,
        0x12345000, 0x12345000);
INSERT zeros VALUES (0x123, 0x123, 0x123, 0x123);
INSERT zeros VALUES (0x0, 0x0, 0x0, 0x0);
INSERT zeros VALUES ('002710000000ae1b',
'002710000000ae1b', '002710000000ae1b',
'002710000000ae1b');
SELECT * FROM zeros;

bnot

bnull

vbnot

vbnull

0x1234500000

0x1234500000

0x12345000

0x12345000

0x0123000000

0x0123000000

0x0123

0x0123

0x0000000000

0x0000000000

0x00

0x00

0x3030323731

0x3030323731

0x3030323731

0x3030323731

Because each byte of storage holds 2 hexadecimal digits, Sybase IQ expects binary entries to consist of the characters “0x” followed by an even number of digits. When the “0x” is followed by an odd number of digits, Sybase IQ assumes that you omitted the leading 0 and adds it for you.

Input values “0x00” and “0x0” are stored as “0x00” in variable-length binary columns (VARBINARY). In fixed-length binary columns (BINARY), the value is padded with zeros to the full length of the field:

INSERT zeros VALUES (0x0, 0x0, 0x0, 0x0);
SELECT * FROM zeros 

bnot

bnull

vbnot

vbnull

0x0000000000

0x0000000000

0x00

0x00

If the input value does not include “0x”, Sybase IQ assumes that the value is an ASCII value and converts it. For example:

CREATE TABLE sample (col_bin BINARY(8));
INSERT sample VALUES ('002710000000ae1b');
SELECT * FROM sample;

col_bin

0x3030323731303030

NoteIn the above example, ensure you set the string_rtruncation option to “off”.

When you select a BINARY value, you must specify the value with the padded zeros or use the CAST function. For example:

SELECT * FROM zeros WHERE bnot = 0x0123000000;

or :

SELECT * FROM zeros WHERE bnot = CAST(0x0123 as binary(5));

Loading ASCII data from a flat file

Any ASCII data loaded from a flat file into a binary type column (BINARY or VARBINARY) is stored as nibbles. For example, if 0x1234 or 1234 is read from a flat file into a binary column, Sybase IQ stores the value as hexadecimal 1234. Sybase IQ ignores the “0x” prefix. If the input data contains any characters out of the range 0 – 9, a – f, and A – F, the data is rejected.

Storage size

Table 3-3 lists the storage size of binary data.

Table 3-3: Storage size of binary data

Data type

Column definition

Input data

Storage

VARBINARY

width of (32K – 1) bytes

(32K – 1) bytes binary

(32K – 1) bytes

VARBINARY

width of (32K– 1) bytes

(64K – 2) bytes ASCII

(32K – 1) bytes

BINARY

width of 255 bytes

255 bytes binary

255 bytes

BINARY

width of 255 bytes

510 bytes ASCII

255 bytes

Platform dependence The exact form in which you enter a particular value depends on the platform you are using. Therefore, calculations involving binary data might produce different results on different machines.

For platform-independent conversions between hexadecimal strings and integers, use the INTTOHEX and HEXTOINT functions rather than the platform-specific CONVERT function. For details, see the section “Data type conversion functions”.

String operators

The concatenation string operators || and + both support binary type data. Explicit conversion of binary operands to character data types is not necessary with the || operator. Explicit and implicit data conversion produce different results, however.


Restrictions on BINARY and VARBINARY data

The following restrictions apply to columns containing BINARY and VARBINARY data:


Compatibility

The treatment of trailing zeros in binary data differs between Sybase IQ, SQL Anywhere, and Adaptive Server Enterprise:

Table 3-4: Treatment of trailing zeros

Data type

Sybase IQ

SQL Anywhere

ASE

BINARY NOT NULL

Padded

Not padded

Padded

BINARY NULL

Padded

Not padded

Not padded

VARBINARY NOT NULL

Truncated, not padded

Truncated, not padded

Truncated, not padded

VARBINARY NULL

Truncated, not padded

Truncated, not padded

Truncated, not padded

Adaptive Server Enterprise, SQL Anywhere, and Sybase IQ all support the STRING_RTRUNCATION database option, which affects error message reporting when an INSERT or UPDATE string is truncated. For Transact-SQL compatible string comparisons, set the STRING_RTRUNCATION option to the same value in both databases.

You can also set the STRING_RTRUNCATION option ON when loading data into a table, to alert you that the data is too large to load into the field. The default value is ON.

Bit operations on binary type data are not supported by Adaptive Server Enterprise. SQL Anywhere only supports bit operations against the first four bytes of binary type data. Sybase IQ supports bit operations against the first eight bytes of binary type data.

UNIQUEIDENTIFIER Used for storage of UUID (also known as GUID) values. The UNIQUEIDENTIFIER data type is often used for a primary key or other unique column to hold UUID (Universally Unique Identifier) values that can be used to uniquely identify rows. The NEWID function generates UUID values in such a way that a value produced on one computer does not match a UUID produced on another computer. UNIQUEIDENTIFIER values generated using NEWID can therefore be used as keys in a synchronization environment.

For example, the following statement updates the table mytab and sets the value of the column uid_col to a unique identifier generated by the NEWID function, if the current value of the column is NULL.

UPDATE mytab
    SET uid_col = NEWID()
      WHERE uid_col IS NULL

If you execute the following statement,

SELECT NEWID()

the unique identifier is returned as a BINARY(16). For example, the value might be 0xd3749fe09cf446e399913bc6434f1f08. You can convert this string into a readable format using the UUIDTOSTR() function.

UUID values are also referred to as GUIDs (Globally Unique Identifier).

The STRTOUUID and UUIDTOSTR functions are used to convert values between UNIQUEIDENTIFIER and string representations.

UNIQUEIDENTIFIER values are stored and returned as BINARY(16).

Because UNIQUEIDENTIFIER values are large, using UNSIGNED BIGINT or UNSIGNED INT identity columns instead of UNIQUEIDENTIFIER is more efficient, if you do not need cross database unique identifiers.


Standards and compatibility for UNIQUEIDENTIFIER


See also

For more information related to UNIQUEIDENTIFIER: