Working with Collations (SQL Server Compact)

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales.

SQL Server Compact provides support for Windows collations. For a list of Windows collation names supported in SQL Server Compact, see the Supported Collations (SQL Server Compact).

SQL Server Compact 3.5 Service Pack 1 (SP1) supports setting collations at the database level. In other words, all string columns (columns of data types, nchar, nvarchar, and ntext) in user tables in the database have the same case sensitivity setting as specified in the database level. In addition, indexes on string columns have the same case sensitivity setting as specified in the database level.

Object names, such as table names, language keywords, functions, and views, are not treated as case-sensitive in a SQL Server Compact database, even if the collation is case-sensitive. This feature is not consistent with SQL Server, but this behavior is consistent with the SQL standard specifying that the collation of a database is used only for user data comparisons.

Connecting to Case Sensitive Databases

You can connect to any case-sensitive SQL Server Compact database just like you connect to any other SQL Server Compact database.

The SP1 release of SQL Server Compact 3.5 provides a support for a new Boolean connection string property, "Case Sensitive", or "SSCE:Case Sensitive", to determine whether or not the database collation is case sensitive. If you try to connect to an existing SQL Server Compact database with a "Case Sensitive" property in the connection string, the SQL Server Compact 3.5 SP1 ignores this setting. In other words, the "Case Sensitive" property is a database creation-time option and is ignored when connecting to an existing database. If you do not specify the case sensitivity when creating a database, by default a case-insensitive database is created.

The Case Sensitive property is a new feature in SQL Server Compact 3.5 SP1 and is not supported in any earlier versions.

Creating Case-Sensitive Databases

The SP1 release of SQL Server Compact 3.5 supports specifying case-sensitive collations for new databases only through the API calls.

You can use a new Boolean connection string property, "Case Sensitive", or "SSCE:Case Sensitive", to determine whether or not the database collation is case sensitive. Similarly, a new property DBPROP_SSCE_DBCASESENSITIVE as part of DBPROPSET_SSCE_DBINIT property set determines whether or not the database collation is case sensitive.

When you set Case Sensitive to true, the indexes on string columns are rebuilt in the database.

Another way to specify a collation for a new database is to use the COLLATE (SQL Server Compact) clause of the CREATE DATABASE (SQL Server Compact) statement. In this case, the case-insensitive (CI) option is the only option.

Merge Replication and Case-Sensitive Collations

SQL Server publishers and SQL Server Compact subscribers support merge replication regardless of case sensitivity settings. In order to support backward compatibility, no scenarios are explicitly blocked.

The SP1 release of SQL Server Compact 3.5 supports replication of case-sensitive SQL Server Compact databases against both case-sensitive and case-insensitive server databases. The following table describes all possible scenarios:

For more information about merge replication, see Using Merge Replication. For more information about how to create a case-sensitive database by using the Replication object, see How to: Create a Database by Using the Replication Object (Programmatically).

See Also

Concepts

International Considerations (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)