NCHAR and NVARCHAR data types

The character data types CHAR, LVARCHAR, and VARCHAR support code-set order collation of data. This is the order in which the characters are defined within in the code set of the database locale that the DB_LOCALE environment variable specifies. The default (U.S. English) locale is an example of a locale that uses the code-set order of collation for sorting CHAR, LVARCHAR, and VARCHAR string values.

For information on how the settings (or default values) of the DB_LOCALE, CLIENT_LOCALE , and SERVER_LOCALE environment variables determine which locale is used for collation, see the HCL OneDB™ GLS User's Guide.

Some locales, however, specify an order of collation that is not identical to the code-set order. To support any locale-specific order of collation, you can use the NCHAR and NVARCHAR data types. The NCHAR data type is a fixed-length character data type that supports localized collation. The NVARCHAR data type is a varying-length character data type that can store up to 255 bytes of text data and supports localized collation. In locales where the code set defines no localized order of collation, such as the default locale, there is no difference between the CHAR and NCHAR data types, nor between the VARCHAR and NVARCHAR data types, except in case-insensitive databases.

In databases created with the NLSCASE INSENSITIVE property, values of these data types are stored exactly as they are loaded into the database, but in data processing operations, including comparison and collation of NVARCHAR and NCHAR strings, the database server ignores letter case, ordering the data values without respect to or preference for case. For example, the NCHAR or NVARCHAR string "PH" might precede or follow "pH" or "ph" in the collated list, in which these three strings are considered duplicates, depending on the order in which these values are retrieved. For more information about NCHAR or NVARCHAR data processing in case-insensitive databases, see Specifying NLSCASE case sensitivity, Duplicate rows in NLSCASE INSENSITIVE databases, and NCHAR and NVARCHAR expressions in case-insensitive databases.

For NCHAR or NVARCHAR values, the SET COLLATION statement of SQL can override the localized collation order of the current session by specifying another locale. Indexes on NCHAR or NVARCHAR columns sort values according to the localized collation order that was in effect when the index was created, if that is different from the current collation order. For more information about how the SET COLLATION statement can affect the sorting behavior of indexes, constraints, cursors, prepared objects, and SPL routines, see Collation Performed by Database Objects.

If you specify no parameters in CREATE TABLE or ALTER TABLE statements that declare VARCHAR or NVARCHAR columns, then the new columns default to a max size of 1 byte and a reserve size of zero.