Working with Case Sensitive SQL Compact databases

Up until version 3.5 SP1, SQL Compact have always been case in-sensitive, meaning that a string stored as "Albert" is considered the same as a string stored as "albert" in terms of sorting and selecting etc.

With 3.5 is is now possible to create Case Sensitive SQL Compact databases. This is always done a file creation time, and covers data in all tables in the entire database file.

When working with databases fro SQL Server 2008 Management Studio and VS 2008 SP1, the new database dialog has been updated to include a checkmark for "Case sensitive". In code, add "Case sensitive=true" to the connection string used for creating the database file.

The following testing is done using SQL 2008 SSMS (RC0).

Let's create a table:

CREATE TABLE CsTest ( TestVal nvarchar(50) NOT NULL )

And add some rows:

INSERT CsTest (TestVal) VALUES ('Albert')
INSERT CsTest (TestVal) VALUES ('albert')

So previously the statement below would have returned 2 rows:

SELECT * FROM CsTest WHERE TestVal = N'albert'

But as the database is case sensitive, only a single row is returned!

If we try to open this database in VS 2008 RTM (with only SQL Compact 3.5 RTM (version 3.5.5386.0) installed) - the engine will throw error 28609:
You are trying to access an older version of a SQL Server Compact 3.5 database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact 3.5 database, run Compact/Repair. (Not extremely helpful)

UPDATE: Converting a database to case sensitive:

This can be done with the Compact command as follows (in this sample Northwind.sdf is converted to a case sensitive database):

SqlCeEngine engine = new SqlCeEngine("Data Source=Northwind.sdf");
engine.Compact("Data Source=; Case Sensitive=True;");