SQL Server Compact 3.5 vs 4.0

[jeremyw]

Imagine someone has multiple installed versions (3.5 and 4.0) of SQL Server Compact. When using SDAC to create a new SQL CE database on this computer, which version will be used? Is this hard-coded, or is there a way to optionally select which version of SQL CE to use? Are there any functions we can call to determine which versions are installed (if any)?

We've been using SQL CE 3.5 (SP1 I think) up until now, but I'm wondering if we should make the switch to 4.0 (I also just downloaded 4.0 SP1). Have you experienced any significant differences (good or bad) with the newer versions?

Among other changes, I've read there are significant encryption changes in 4.0 (support for SHA2?). We use encrypted databases, so I wanted to make sure there is full support for the SQL CE 4.0 encryption.

[azyk]

Imagine someone has multiple installed versions (3.5 and 4.0) of SQL Server Compact. When using SDAC to create a new SQL CE database on this computer, which version will be used? Is this hard-coded, or is there a way to optionally select which version of SQL CE to use? Are there any functions we can call to determine which versions are installed (if any)?

When the TMSCompactConnection.Options.CompactVersion property is set to cvAuto, SDAC will detect the SQL Server Compact version, for which a database will be created in the following order: 4.0 -> 3.5 -> 3.0 . I.e., if the 3.5 and 4.0 versions are installed simultaneously on the PC, SDAC will create a database for the version 4.0.

In order for SDAC to use a definite SQL Server Compact version, set the TMSCompactConnection.Options.CompactVersion option to one of the following values: cv40, cv35 or cv30.

To learn how to detect the installed SQL Server Compact versions, read the following article at the Microsoft website: http://support.microsoft.com/kb/950550

We've been using SQL CE 3.5 (SP1 I think) up until now, but I'm wondering if we should make the switch to 4.0 (I also just downloaded 4.0 SP1). Have you experienced any significant differences (good or bad) with the newer versions?

This question doesn't concern to SDAC functionality. Please forward this question with all your product requirements to SQL Server Compact developers - Microsoft.

Among other changes, I've read there are significant encryption changes in 4.0 (support for SHA2?). We use encrypted databases, so I wanted to make sure there is full support for the SQL CE 4.0 encryption.

At present SDAC does not support this functionality. You can suggest this feature at our UserVoice ( http://devart.uservoice.com/forums/1046 ... components ). If the suggestion gets a lot of votes, we will consider the possibility to implement it.

[jeremyw]

Thank you for the quick and helpful response! I was digging through the SDAC documentation trying to find some of these answers, but I obviously didn't dig deep enough.

Incidentally, after seeing this, I looked back through the SDAC help. We're still using SDAC 6.11.23, but the help page for TMSCompactConnectionOptions.CompactVersion only references SQL CE 3.0 and 3.5. It's a minor issue, but it might be worth adding references to 4.0 now (especially since it takes priority over 3.5 by default).

[azyk]

Thank you for your comment on our online documentation. We will correct this shortly.