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

Question

I was preparing to make a presentation on SQL Server Compact Edition 3.5 (SP1 - recently released), and I was wondering if I planned (or planned to design) a system with SQL Server CE 3.5 (or earlier)?

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

Version 3.5 looks pretty functionally rich compared to its predecessors, is it ready for serious consideration (now that it supports LINQ to SQL, identification columns and a set of important T-SQL)?

Could you use it if you needed a lightweight database with a low database size (especially if it needed support on mobile devices and desktop systems), or are there better options?

Answer 1

At work, we recently had to migrate a project designed for SQL Server Express for SQLCE. At first I thought it would be horrible, mainly because there are no stored procedures. As soon as we began to delve into him, although we saw what he was offering, we were convinced that this was the right move. It supports LINQ, many requests for data took MUCH faster than we thought. Insert and update were a bit slower, but really not so bad. The only problems we encountered were A) Management Studio 2005 does not support it, and although 2008 is very limited. Not sure why, hopefully, they will change this for future releases, but it cursed me. B) When you try to migrate an existing SQL Server database to SQLCE, there is no easy way to create a schema from an existing one and move all the data. You may have to flip your own tool to do this (we should have).

All that was said, the end result was amazing. Installation time was reduced to the third now that users did not have to install SQL Express, and the application worked much more smoothly on machines with less memory. All in all, I was amazed at how well it works.

Answer 2

I tried several versions of SQLCE and always used the excellent ADO.NET SQLite implementation - it is easier, faster and easier to use (at least for my purposes), and it carries the cross on almost all platforms.

Answer 3

I am currently using SQL CE with NHibernate in business applications where the SQL CE database is synchronized with Sync Framework via WCF on SQL Server 2008. Works like a charm! especially creating a database from nhibernate mapping files is just awesome.

but even when using linq2sql it works very well!

Answer 4

I tried SQLCE several times. Always finished using something else (Firebird (built-in), SQLite, VistaDB). IMO SQLCE is a method of limited use in more or less complex scenarios. Although I do not have real experience with the latest version (3.5), but I suspect that it still sucks :)

Answer 5

I helped develop and maintain PDA software using SQL Server CE 2.0. I think that everything is in order, as long as you know where the borders are.

Answer 6

I used SQLCE once, for the exercise that I received at the interview, and that was enough for that. Now that I need this functionality, I use sqlite or firebird.

The usability of SQLCE for this exercise was very good. It is fast, has a good visual editor and is well integrated into Visual Studio.

On the bottom line: if you can handle the limitations of SQLCE, and you are sure that you do not need more than that, use it.

Answer 7

If you are using on a mobile device, you cannot use Linq for SQL.

We use it, and that’s good, but Linq to Dataset is not as good as Linq to SQL

Answer 8

We used SQLCE (version 3.0, I believe) in the project a year or 2 ago.
The problem that I encountered is that it is needed for installation, there was no XCOpy for deployment. Also the performance was terrible. we did about 20 inserts per second, which is probably beyond his capabilities, but it definitely could not handle it. Performance and deployment issues have convinced me that there are better alternatives. my next project will use either SQLite or VistaDB.

I used SQLite on Mac and IPhone for development, and it works great on both of these platforms.

Answer 9

There is a problem if you are using SQLCE 3.5 SP1 with Entity Framework. Basically, a problem arises when passing some parameters to the request. A workaround is to uninstall SQLCE and install another version released as a fix. The problem is trying to include SQLCE in the installation project. More information about this topic 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 Service Pack 1 (SP1) with an entity infrastructure will not work on 64-bit systems unless you create it for the target x86 ( http://social.msdn.microsoft.com / forums / en-US / adodotnetentityframework / thread / aa5850bb-34fe-4064-82b0-4a7cac675c91 / )