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).  

Starting with the release of SQL Server Compact 3.5 Service Pack 1 (SP1), SQL Server Compact supports case sensitive collations. Case sensitivity is set 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 at the database level. In addition, indexes on string columns have the same case sensitivity setting as specified at 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.

Starting with the release of SQL Server Compact 3.5 SP1, SQL Server Compact provides 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, 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 introduced in SQL Server Compact 3.5 SP1 and is not supported in any earlier versions.

Creating Case-Sensitive Databases

SQL Server Compact supports specifying case-sensitive collations for new databases only through the API calls. This feature is only supported in SQL Server Compact 3.5 SP1 and later versions.

You can use the new Boolean connection string property, "Case Sensitive", or "SSCE:Case Sensitive", to determine whether or not the database collation is case sensitive. For native programming, a new property DBPROP_SSCE_DBCASESENSITIVE, which is 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.

You can also specify collation for a new database by using the COLLATE (SQL Server Compact) clause of the CREATE DATABASE (SQL Server Compact) statement. However, in this case, the case-insensitive (CI) option is the only option supported.

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).

Concepts

International Considerations (SQL Server Compact)