SQL CE v3.5 SP1 Features

Before we start, as usual the disclaimer holds J and plans may change.

SQL CE v3.5 SP1 is getting released with SQL Server 2008 and Visual Studio 2008 SP1.

Q: Why blog about a service pack, it is just part of life cycle?

A: There are some goodies that are coming along with this service pack. Hence, the blog post J

The goodies:

1) Case Sensitive Databases

2) Native 64-bit support

3) Platform Builder Integration (Catalog Items)

Legend:

Case Sensitive – CS, Case Insensitive – CI,

Windows Desktop Native 64-bit engine – win64

Windows Desktop Native 32-bit engine – win32

Case Sensitive Databases (a.k.a. Case Sensitive Collations):

With SQL CE v3.5 SP1 onwards, we now support case-sensitive databases. We have done things little different. The metadata like database object names (i.e. table name, column names, etc) are always case insensitive irrespective of the sensitivity of the database. The case sensitivity is applicable only to real user data. Also note that the case sensitivity is always at database level (not table or column or query level).

To create case sensitive database, add “Case Sensitive=true” to connection string. If you are an OLEDB developer, please use property DBPROP_SSCE_DBCASESENSITIVE.

If you want to know the case sensitivity of the database already created, use SqlCeConnection.GetDatabaseInfo API.

Merge Replication

You can Merge Replicate SQL CE DB in all four combinations of case sensitivity with SQL Server. That is,

SQL CE CI DB – SQL Server CI DB – Already supported

SQL CE CI DB – SQL Server CS DB – Already supported

SQL CE CS DB – SQL Server CI DB – New combination

SQL CE CS DB – SQL Server CS DB – New combination

However, in the cross combinations users have to be cognizant about the sensitivity mismatch and put the data in to the DB which is acceptable to both of the sync end-points in terms of constraints, indexes …etc.

ADO.NET Sync Services (aka: OCS)

This is not supported with case sensitivity. Though you can use CS DB with sync services, you cannot really have the rows that differ only in case with sync services. This is not tested and hence cannot guaranty.

Remote Data Access

RDA has been in maintenance mode for almost two versions and we don’t have any plans to make it work with new scenarios and so is not supported. It might work, but officially we don’t support it.

Tools

Both SQL Server 2008 Management Studio and Visual Studio 2008 SP1 can be used with CS DB. You can create SQL CE CS DB using these tools UI.

Compatibility FAQ

#1: Since, SQL CE v3.5 SP1 MSI always upgrades SQL CE v3.5 RTM MSI. So, this is not a user scenario when using MSI installation. However, if you have private deployments of SQL CE v3.5 RTM for your app and you are also using VS 2008 SP1 or SQL Server 2008 to operate on the database, then this is an important point to note and we recommend you to push SQL CE v3.5 SP1 bits in your private deployments to help the end user. Also there is a good chance that the private deployment to start working with central deployment because of CLR assembly binding semantics.

Native 64-bit Support

With SQL CE v3.5 SP1, we will be providing native 64-bit support. SQL CE v3.5 SP1 64-bit native engine would be available as a web download. We don’t have a different tooling support for our native 64-bit. Note that, you can still have a box with SSMS 2008 and our native 64-bit working on a same database. You really do not need a 64-bit tooling as databases are compatible.

Platform Builder Integration

SQL CE v3.5 SP1 would be available as a catalog item in Platform Builder Versions 5.0, 6.0.

Thanks for reading!

Laxmi Narsimha Rao Oruganti