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.

Syntax

BINARY [ ( length ) ]

VARBINARY [ ( max-length ) ]

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, always specifying an even number of characters for BINARY and VARBINARY column length is recommended. If you enter a value longer than the specified column length, Sybase IQ truncates the entry to the specified length without warning or error.

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.) For more information, see Large Objects Management in Sybase IQ.

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;

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, 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 WHERE bnot = 0x00;

If the input value does not include the “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;

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

• You cannot use the aggregate functions SUM, AVG, STDDEV, or VARIANCE with the binary data types. The aggregate functions MIN, MAX, and COUNT do support the binary data types BINARY and VARBINARY.

• HNG, WD, DATE, TIME, and DTTM indexes do not support BINARY or VARBINARY data.

• Only the default index and CMP index types are supported for VARBINARY data greater than 255 bytes in length.

• Bit operations are supported on BINARY and VARBINARY data that is 8 bytes or less in length.

Adaptive Server Enterprise, Adaptive Server 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 OFF.

Bit operations on binary type data are not supported by ASE. ASA only supports bit operations against the first four bytes of binary type data. IQ supports bit operations against the first 8 bytes of binary type data.