SQL LocalDB vs SQL Server CE

Question

I have a small project that will have 1 user on 1 computer. The database will be rather small (probably less than 1 mb of data). I plan to use WPF and Entity Framework for that project.

I came up with 2 potential database solutions for my project : SQL Server CE and SQL Server Express LocalDB (with SQL Server 2012). I have never worked with any of those, I'm more used to work with the full SQL Server 2008 installation.

I also want it to be easy to install on the client. Ideally I'd like to just throw a bunch of files on a folder (along with my .exe file).

So anybody here could suggest me the best technology to use?

Thanks!

Themselves

Answer 1

See the Introducing SQL Server Express Local DB Runtime presentation - gives a great overview.

The huge benefit of LocalDB is that it's real SQL Server - it's a special version of SQL Server Express, but it basically supports everything that "real" SQL Server has - spatial data types, stored procedures - you name it.

SQL Server Compact Edition on the other hand is a very much scaled down version - lot of features and datatypes aren't supported. It's smaller and more "agile" - but it lacks a lot of punch.

I personally would pick SQL Server Express with the LocalDB runtime over SQL Server CE 4 - unless you specifically need the tiny size of SQL Server CE (e.g. on a mobile device).

Also see a comparison between SQL Server CE 3.5, CE 4.0, SQL Server Express 2008 R2 and SQL Server 2012 Express LocalDB on ErikEJ's excellent Everything SQL Server Compact web site.

Expensive

Another good link blogs.msdn.com/b/jerrynixon/archive/2012/02/26/… – Timtima

Answer 2

It depends, but based on your requirements (ie very small amount of data and XCopy installation), your only option is SQL Server Compact, as the footprint of SQL Compact on disk is about 18 MB vs 160 MB with LocalDB, and LocalDB requires an administrator install (comes as an MSI only) - but if SQL Server compatibilty is more important, LocalDB is the best choice (as marc_s notes)

Rabideau

Answer 3

Forgetting all the vague "it's easier", "it's smaller", "it lacks punch" or "it's lighter" answers (no offense), I think one should focus on the main difference between the two when faced with the choice:

• LocalDB is out-of-process.
• CE is in-process.

Running as part of the application process or not has many implications which probably should be explored in separate questions.

The most obvious one though is that if you need to access the database from multiple applications using an in-process DBMS (such as CE), all applications will access the database file directly using their own cpu-time. Synchronizing them is really hard, and realizing this helps understanding why CE has less features (that and many other things).

Also note that while one may worry that multiple processes running the same CE code might take up more memory than necessary, remember that the operating system is smart enough to share pages from its page cache between multiple processes, so that one is really not an issue.

LocalDb is simply a way of managing a normal Express instance automatically, and on a per-user basis (it's not different from SQL Server Express, it's the same thing).

Plumbaginaceous

Answer 4

I'm really going to have t go with ErikEJ here. I don't have the reputation to vote up his answer, but given your requested target SQL CE is really the quickest in and out. Many of the comparison issues given link provided by marc_s on SQL CE's "missing features" VS LocalDB are mitigated by virtue of your using EntityFramework (Procedural T-SQL is not supported, but if you use EF and Lambda and LINQ, that won't really be an issue).

Penrod

Yep. I don't see a reason why you'd need stored procedures when you're working with a local file. I'd also go with CE. – Nimbus

Answer 5

See this page for some background behind why LocalDB was created

It was created specifically for developers. It is easy to install, requires no management, yet compatible with other SQL Server editions at the API level. In this regard it is like a lightweight version of the free SQL Server Express edition.

While this seems similar to SQL Server Compact, there are differences:

• Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.
• Disk usage: all SQL Server Compact binaries amount to some 4MBs, while LocalDB installation takes 140MBs.
• Features: SQL Server Compact offers core RDBMS functionality like querying, while LocalDB provides a much richer set of features, including Stored Procedures, Geometry and Geography data types, etc.

The way I see it, LocalDB is often used for offline development to ensure that the code you develop is 100% compatible with your production SQL Server database.

Islington

Very good answer, it would have been excellent if you address the deployment differences (if any). Thanks. – Obryant

Minor note: The link at the top is no longer working. – Obryant

Here's a copy of the link as archived by the Wayback Machine: web.archive.org/web/20110716081753/http://blogs.msdn.com/b/… – Islington

Answer 6

Also, if hiding your intellectual property/schema/data from easy access by users is important, SQL CE can be password protected/encrypted. SQL Local db can be easily opened from management studio.

Owen

If the application can open the DB, so can the user. – Barner

I think that is why he said hiding from easy access. A normal user would have to know the db password to get inside. – Szymanowski

unfortunately, this point is the most expensive one that makes me cannot take the decision of moving to LocalDB till now :( – Ahoufe