Restrictions on SET COLLATION

Although SET COLLATION enables you to change the collating order of the database server dynamically within a session, you should be aware of these limitations on the effects of the SET COLLATION statement.

• Only collation performed by the database server is affected. Client processes that sort data are not affected by SET COLLATION.
• Only the current session is affected. Other sessions are not affected directly by your SET COLLATION statements (but any database objects that you create will sort in their creation-time collating order).
• Changing the collating order does not change the code set. The database server always uses the code set specified by DB_LOCALE.
• Only NCHAR and NVARCHAR values sort in locale-specific order.

Because SET COLLATION changes only the collating order, rather than the current locale or code set, you cannot use this statement to insert character data from different locales, such as French and Japanese, into the same database. You must use Unicode if the database needs to store characters from two or more languages that require inherently different code sets. The database can store characters from the dissimilar character sets of more than one natural language only if you set DB_LOCALE to a Unicode locale when the database is created.