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 4.0 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.
SQL Server Compact 4.0 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 4.0 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 4.0 and is not supported in any earlier versions.
Creating Case-Sensitive Databases
SQL Server Compact supports specifying case-sensitive collations for new databases through the API calls.
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.
Case sensitive databases can be also created by using Visual Studio 2010 SP1 as given below:
In the Server Explorer, right-click on the Data Connections node and select Add Connections from the context sensitive menu.
In the Add Connection dialog ensure that the Data Source is Microsoft SQL Server Compact 4.0. If the data source is not SQL Server Compact 4.0 follow the steps below:
Click on the Change button in the Add Connection dialog.
Select Microsoft SQL Server Compact 4.0 from the Data Sources.
If you want to use SQL Server Compact 4.0 by default check the box, Always use this selection.
Click OK on the Change Data Source dialog.
In the Add Connection dialog box, click Create.
In the dialog box, Create New SQL Server Compact Database fill the database path and check the box Case Sensitive. The database that is created at the specified location is a case sensitive database.