Specify a data type.


Syntax

<data type> ::=

<character set clause> ::=

|

CHARACTER SET <character string literal>

CODEPAGE <unsigned integer>

<collate clause> ::=

|

COLLATE <character string literal> [ <compare flag>... ]

LOCALE <unsigned integer> [ <compare flag>... ]

<compare flag> ::=

|

|

|

|

IGNORE KANA TYPE

IGNORE NONSPACE

IGNORE SYMBOLS

IGNORE WIDTH

USE STRING SORT


Character sets and collations

NexusDB V3 uses the available Windows OS codepages and locales to define the encoding and collation for the character string types.

CHARACTER SET and CODEPAGE are used interchangeably to specify the encoding used by the character string type.
The national character string type implicitly defines codepage UTF16.
If a character set is not specified, then the default codepage associated with the specified collation is implicit.
If neither an explicit nor implicit character set is defined, then no encoding takes place.

Tip: You can query the #CODEPAGES system table to get a list of supported character sets.

COLLATE and LOCALE are used interchangeably to specify an explicit collation for the character string and national character string types.
If a collation is not specified for the character string type, then the collation is byte order.
If a collation is not specified for the national character string type, then the collation is locale-neutral.

Tip: You can query the #COLLATIONS system table to get a list of supported collations.

Note: The compare flags are arguments used in standard Windows API calls. Please check your Windows OS documentation for the meaning of these flags.


Result data types of aggregations

The result data type of an aggregation over values of compatible data types, such as CASE expressions and columns in the result of a query expression with UNION, EXCEPT and INTERSECT, is determined as follows:

All data types in the set of data types shall be comparable.
If any of the data types in the set are character string, then all data types shall be a character string type. The result data type is the character string type with highest precedence:
If at least one of the data types in the set is a character large object type, then the result data type is a character large object type. NATIONAL CHARACTER LARGE OBJECT takes precedence over CHARACTER LARGE OBJECT.
If at least one of the data types in the set is a variable-length character string, then the result data type is a variable-length character string with maximum length equal to the maximum length of the data types in the set. NATIONAL CHARACTER VARYING takes precedence over CHARACTER VARYING.
Otherwise the result is a fixed-length character string with length equal to the maximum length of the data types in the set. NATIONAL CHARACTER takes precedence over CHARACTER.
If any of the data types in the set are binary string, then all data types shall be binary string. The result data type is BINARY LARGE OBJECT.
If all data types in the set are exact numeric, then the result data type is an exact numeric type with precision not less than the highest precision of the data types in the set and scale equal to the maximum scale of the data types in the set.
If any of the data types in the set are approximate numeric, then the result data type is an approximate numeric type with precision not less than the highest precision of the data types in the set.
If any of the data types in the set are a datetime type, then all data types in the set shall be a datetime type having the same datetime fields, which is also the data type of the result.
If any of the data types in the set are boolean, then all data types shall be boolean. The result data type is BOOLEAN.

Special data types

GUID is a NexusDB-specific 16 byte binary string type used to define a Globally Unique Identifier.

       The NEWGUID function can be used to generate GUID values.

BYTEARRAY is a NexusDB-specific binary string type with a maximum length of 64KB.
RECREV is a special NexusDB-specific 32-bit unsigned integer type which is incremented automatically each time the row is updated.


Conformance

SQL:2003 standard

NexusDB extensions

-

-

-

-

-

-

-

-

-

-

Core SQL

Feature F421 "National character"

Feature T041 "Basic LOB data type support"

Feature T031 "BOOLEAN data type"

GUID

BYTEARRAY

RECREV

CODEPAGE

LOCALE

Compare flags in the collate clause

Overview of Predefined Data Types

String Types

Numeric Types

Boolean Types

DateTime Types

Home | Site Contents | Documentation | NexusDB Manual V4 | SQL Reference