Open topic with navigation
Character types are data types designed for storing text with either a fixed or variable number of characters.
|STRING||A NuoDB extension. NuoDB handles string types as potentially unlimited in length. NuoDB recommends that you use String types instead of Character types. Characters are all stored in UTF-8. An empty string or a string containing only spaces and assigned to a numeric value becomes zero.||
|CHARACTER||variable-length character string||
|CHARACTER LARGE OBJECT||unlimited character length||
|NATIONAL CHARACTER||specifies predefined national character set, synonymous with char.||
|TEXT||unlimited character length||
Note: For character varying data types, if
n equals 0, the default length is 1.
Strings are stored within the entire data record for a row. Character Large Objects (CLOBs) are stored in separate atomThe internal object structure representing all data and metadata in a NuoDB database. Atoms are self-coordinating objects that represent specific types of information (such as data, indexes, or schemas). Atoms are stored in memory on both TEs and SMs or serialized over the network and stored to disk by SMs. See also Storage Manager (SM).s. CLOBs are not versioned. So there is only one version at any one time for a CLOB field in a record. Strings have multiple versions tracking any changes to that particular field in a record. Strings are encoded in the table row record and is cloned every time it is updated. If you have a very large string which is updated often, the row record can become very large and cause network issues. CLOBs are stored as a dedicated data record with a pointer to the record in the table row record and updates to CLOBs do not result in large row records. As a result, the choice between using CLOBs and strings depends on the application access and update pattern.
CLOB values are stored separately from the rest of the contents of their record, and only a reference to the BLOB value (for more information, see SQL Binary Types) is kept in the record. Only
SELECT statements which request the contents of the CLOB field will load it, and changes to other fields in the record will not cause the CLOB value to be rewritten. However, because it's stored separately it does take slightly more time to retrieve the value when it is required.
String literals are specified as single quoted strings. Example:
SQL> CREATE TABLE tbl(x STRING);
SQL> INSERT INTO tbl VALUES ('abc');
SQL> SELECT * FROM tbl; X --- abc
String literals may be represented as
N' as in this example:
SQL> INSERT INTO tbl VALUES (N'def');
SQL> SELECT * FROM tbl; X --- abc def
Note: Since all NuoDB characters are Unicode characters, the
N prefix is redundant. However, NuoDB still supports the
N'string' syntax (when using ASCII characters) for compatibility with other databases.