Would you use Sql Server Compact for a desktop application?

Question

Does it make sense to use SQL Server Compact for a desktop application?

• How much data can it handle?
• Is it possible to use it with multiple users? What are the constraints?
• What happens when database files are broken? Is it possible to programmatically recover or would it be easy to solve the problem remotely?

Answer 1

I certainly would (and do). SQL Compact (SQLCE) has a distinct advantage in some scenarios. Now it's in no way meant to be a replacement for SQL Server. It doesn't scale, it isn't made to run thousands of concurrent connections, it's not designed for reporting services, etc, etc.

But it has a tiny footprint. Installing is a matter of just copying over DLLs. Backing it up is a simple file copy. And it can be wicked fast for storage and retrieval if you're using table direct or BCP. Oh, and it's free. If you just need a local data store for an app, SQL Server is a pain to deploy and configure. SQLCE is simple. Plus it can play in the same sandbox as a full SQL Server as a replication subscriber (which most other small DBs can't).

Edit: Forgot to answer your specific questions:

• How much data can it handle? In terms of what? DB size? Throughput? Table size? Row size? My experience is that I've run the DB out to a a few hundred MB with no issue. I think it can hold 2GB. Throughput is directly related to how you're putting data in or taking it out. We're using it in a solution where we're capturing 1k rows a second with no problem. You can have 1024 columns and a max row size of 8k.
• Multiple users? Well it can handle multiple simulataneous connections (as of 3.5), so yes, that should work. I've used multiple connections from one user, never from multiple.
• Broken databases? Define "broken". The engine has a "compact and repair" method. It's a file like any other - how to fix a broken executable? I've not seen any corruptions in the field, if that's what you're asking (well we did on one CE device that has a bad storage card, but that's not the engine's fault).

Answer 2

If you want a small database use something like SQLLite.

But here is an article about using compact edition on a server

Answer 3

I used it for one application and it worked well. The only issue I had is that, for some reason, SQL Server 2005 Management Studio does not support all the designer features for the latest release of SQL Server CE (e.g. you cannot edit tables, for instance). You have to use the 2008 version I think.

It's fast enough, but admittedly I used it for small data sets.