SQL Binary Types

All SQL Binary Types are sequences of octets. They are used for storing data considered to be 'raw' bytes, as opposed to the character strings that are appropriate for storing text. Binary type values have no character set or collation, and sorting and comparison are done based on the numeric values of the stored bytes. When comparing binary type values all bytes are significant.

The following table provides a brief description of, and syntax for, the different SQL binary types:

Name Description Syntax
BLOB Binary Large Object BLOB
Binary Fixed length binary data of length specified in bytes by parameter ( n ). Binary with no length parameter ( n ) specified is defined as a single byte (same as binary(1)). Binary values are not padded when stored. For example, a field declared BINARY(4) but an item inserted is only 3 bytes long, is stored as 3 bytes, not 4. BINARY [(n)]
Variable Binary Variable Binary data types must be defined with a maximum length parameter ( n ). Variable Binary values are not padded when stored and their lengths vary, but cannot exceed the maximum length defined. BINARY VARYING(n)
or
VARBINARY(n)

Storing Values for Binary Types

BLOB values are stored separately from the rest of the contents of their row, and only a reference to the BLOB value is kept in the row. SELECT statements which don't request the contents of the BLOB field don't load it, and changes to other fields in the row don't cause the BLOB value to be rewritten. However, because it's stored separately it does take more time to retrieve the value when it is required. Even though BLOB values are stored separately from the rest of the data in a row, they still participate in Multi-Version Concurrency Control (MVCC) and provide the same transactional consistency guarantees as other types.

In NuoDB, BINARY and BINARY VARYING (VARBINARY) values are stored along with the rest of the row in the table. When you select a row, the BINARY or BINARY VARYING value is read into memory. If you update any field in the row, the entire row is rewritten, including the BINARY or BINARY VARYING field (this is how MVCC ensures transactional consistency).

The BLOB type is intended to store large values, particularly the large values which are not always selected, and even more so the large values in rows where other fields are often updated.

Binary Input Formats

Any type value can be inserted into a binary string field as any value can be cast to binary. Inserting an integer value of type SMALLINT, INTEGER, or BIGINT will store the bytes representing that numeric value in the binary string field. Inserting a character value of type STRING, CHAR, or CHAR VARYING will store the bytes representing that string into the binary string field. Inserting a fixed point value with precision and scale (NUMERIC or DECIMAL), a floating point value (DOUBLE), or a DATE/TIME type will insert the internal representation of that value.

Literals being inserted into a binary string field can be a quoted string, a numeric constant in decimal or hexadecimal format. The hexadecimal value is specified with a leading "0X" or "0x" and the rest of the string is interpreted as a series of two digit hexadecimal values, each representing one byte in the binary string. The hexadecimal values may be upper or lower case. When using the hexadecimal syntax, values that contain an odd number of digits are treated as having an extra leading 0. For example, 0xa and 0xaaa are interpreted as 0x0a and 0x0aaa.

Inserting a literal into a binary field has the following behaviors:

Note: In numeric contexts, hexadecimal values act like integers. In string contexts (binary and char), hexadecimal values act like binary strings, where each pair of hex digits is converted to a character.

Binary Output

By default, all binary fields are printed as hexadecimal values. To print as another type, use CAST.

Example

            CREATE TABLE bin_table (col_bin BINARY(3));
INSERT INTO bin_table VALUES ('abc');
SELECT * FROM bin_table;
 COL_BIN 
 --------
  616263
 
INSERT INTO bin_table VALUES (0x646F67);
SELECT * FROM bin_table;
 COL_BIN 
 --------
  616263 
  646F67 
 
SELECT CAST(col_bin AS STRING) FROM bin_table;
 ---
 abc
 dog