Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Would you use Sql Server Compact for a desktop application? [closed]

Tags:

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?
like image 452
Serhat Ozgel Avatar asked Jan 06 '09 14:01

Serhat Ozgel


People also ask

What is Microsoft SQL Server Compact used for?

Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications.

Is SQL Server Compact deprecated?

In February 2013, Microsoft announced that SQL Server Compact Edition had been deprecated. Although no new versions or updates are planned, Microsoft will continue to support SQL Compact through their standard lifecycle support policy. Extended support for SQL Server Compact 4.0 ended on July 13, 2021.

How do I open Microsoft SQL Server Compact Edition?

(1) Open SQL Server Management Studio, or if it's running select File -> Connect Object Explorer... (2) In the Connect to Server dialog change Server type to SQL Server Compact Edition (3) From the Database file dropdown select <Browse for more...> (4) Open your SDF file.


1 Answers

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 4GB (as of 4.0). 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 do you 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 had a bad storage card, but that's not the engine's fault).
like image 182
ctacke Avatar answered Oct 04 '22 01:10

ctacke