Specifying a Collating Order with SET COLLATION
SET COLLATION replaces the current collating order for NCHAR and NVARCHAR values with that of the specified locale for all database servers previously accessed in the current session.
EXEC SQL set collation "zh_cn.gb18030-2000";If the next operation of a database server in this session sorted NCHAR or NVARCHAR values, the result would follow the Chinese collating order that the SET COLLATION statement specified.
EXEC SQL set no collation;
After SET NO COLLATION executes, subsequent collation in the same session is based on the DB_LOCALE setting. Any database objects that you created using the Chinese collating order, however, such as check constraints, indexes, prepared objects, triggers, or UDRs, will continue to apply Chinese collation rules to NCHAR and NVARCHAR data types.
Collation in an NLSCASE INSENSITIVE database
SET COLLATION "de_de.8859-1";By default, this locale supports case differences between uppercase and lowercase alphabetic characters. For example, two CHAR or LVARCHAR strings of the same letters and in the same order, but with differences in case, such as
'Zug' and 'ZUG',
are treated in LIKE or MATCHES expressions or sorted in ORDER BY clauses
as distinct data values.In
a database created with the NLSCASE INSENSITIVE keywords,
however, collating operations on NCHAR and NVARCHAR data disregard
case differences, so that the database server treats case variants
among strings composed of same sequence letters as duplicates. The
resulting collated list orders these case-insensitive duplicates in
their order of retrieval, so a collated list with case variants of
the string alpha might appear in any order, such
as this order, which disregards variations in case:
alpha Alpha alpha ALPHA Alpha
For more information, see Duplicate rows in NLSCASE INSENSITIVE databases and NCHAR and NVARCHAR expressions in case-insensitive databases.