Has anyone used (or considered using) Sql Server Compact Edition?

Question

Hi, I've been preparing to deliver a presentation on SQL Server Compact Edition 3.5 (SP1 - recently released) and I was wondering if anyone had designed (or contemplated designing) a system with SQL Server CE 3.5 (or earlier versions)?

In particular, has anyone thought of (or experienced) using SQLCE as a solution for offline data storage (as part of a connected system design)?

Version 3.5 looks to be pretty feature rich compared to its predecessors, is it ready for serious consideration (now that it supports LINQ to SQL, identity columns and an assortment of important T-SQL)?

Would you consider using it if you had the need for a lightweight low-footprint file based database (especially if it needed to be supported on mobile devices and desktop systems), or are there better options?

Answer 1

I tried several versions of SQLCE and always ended up using the excellent ADO.NET SQLite implementation - it's lighter, faster, easier to use (at least for my purposes), and it's portable cross almost all platforms.

Answer 2

Tried SQLCE several times. Always ended up using something else (Firebird (embedded), SQLite, VistaDB). IMO SQLCE is way to limited to be useful in more or less complex scenarios. Though I have no real-world experience with latest version (3.5) but I suspect it still sucks :)

Answer 3

I've helped developing and maintaining a PDA software that uses SQL Server CE 2.0. I think it's OK as long as you know where the limits are.

Answer 4

i currently use SQL CE with NHibernate in a business applicatino where the SQL CE database gets synchronized with Sync Framework via WCF to an SQL 2008 server. works like a charm! especially generating the database from nhibernate mapping files is just awesome.

but even when using linq2sql it works pretty fine!

Answer 5

I've used SQLCE once, for an exercise I've received in a job interview, and it was sufficient for just that. Now, whenever I need such functionality, I use sqlite or firebird.

The usability of SQLCE for that exercise was very good. It's quick, has good visual editor, and well integrated into Visual Studio.

Bottom line: if you can handle the limitations of SQLCE, and you're SURE you won't need more than that - use it.

Answer 6

At work, we recently had to migrate a project that was designed for SQL Server Express to SQLCE. At first I thought it was going to be awful, mainly due to the fact that there are no stored procedures. Once we started digging into it though, and saw what it DOES offer, we were convinced it was the right move. It supports LINQ, querying alot of data took MUCH quicker than we thought it would. Inserting and Updating were a little slower, but really not too bad at all. The only problems we ran into really were A) Management Studio 2005 doesn't support it, and although 2008 does, it's very limited. Not sure why, hopefully they'll change that for future releases, but it bugged the hell outta me. B) When trying to migrate an existing SQL Server DB to a SQLCE, there's really no easy way to create the schema from the existing one, and to move all the data. You'll probably have to roll your own tool to do that (we had to).

All that being said though, the end result was amazing. The install time was cut down to a third now that users didn't have to install SQL Express, and the application ran much smoother on machines with less memory. All in all, I was amazed at how well it actually works.

Answer 7

If used on a mobile device then you cannot use Linq to SQL.

We use it and it is nice, but Linq to Dataset is not as nice as Linq to SQL

Answer 8

We used SQLCE (version 3.0 I believe) in a project a year or 2 ago.
Problem I had with it is that it needed an install, wasn't XCOpy deployable. Also the performance was horrible. we were doing about 20 inserts a second, which is probably outside of it's capabilities, but it definitally couldn't handle it. Performance and deployment problems have convinced me that there are better alternatives. my next project will use either SQLite or VistaDB.

I've used SQLite on the Mac, and IPhone for developement, and it performs very well on both of those platforms.

Answer 9

There is a problem if you use SQLCE 3.5 SP1 with Entity Framework. Basically there is a problem when passing some parameters to a query. The work around is to uninstall the SQLCE and install another version released as a hotfix. The problem with that is when trying to include the SQLCE in a setup project. More about the subject can be found in this thread: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/b6bac277-cf66-4c74-a0b3-e48abedbd161

Another strange problem is that SQLCE 3.5 SP1 with entity framework will not work on 64 bits systems unless you build it to target x86 (http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/aa5850bb-34fe-4064-82b0-4a7cac675c91/)