Specifying a Collating Order with SET COLLATION

SET COLLATION replaces the current collating order with that of the specified locale for all database servers previously accessed in the current session. For example, this specifies the collating order of the German language:

EXEC SQL set collation "de_de.8859-1";

If the next action of a database server in this session sorted NCHAR or NVARCHAR values, this would follow the German collating order.

Suppose that, in the same session, the following SET NO COLLATION statement restores the DB_LOCALE setting for the collating order:

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 German collating order, however, such as check constraints, indexes, prepared objects, triggers, or UDRs, will continue to apply German collation to NCHAR and NVARCHAR data types.