Service based database vs SQL Server Compact vs LocalDB?

Question

My goal is to create a stand-alone C# Windows form application with 2-3 tables that will be installed on client machine with a installer file. And I don't want to install SQL Server 2008 software on client machine, I'm using Visual Studio 2013 C# for development.

My options are SQL Server Compct 4.0 but I don't see Visual Studio 2013 has inbuilt support for that, when I add new item to project I don't see Compact (.sdf) file option there.

I've installed third-party SQL Server Compact Toolbox so I can create .sdf file and connectivity but I believe Microsoft is not giving support for compact db now.

I can see service-based database (.mdf) file which is actually a LocalDb\\ something.

So what should I use if I don't want to install SQL Server 2008 on client but want to have db at client software?

If you have only 2-3 tables why don't you just use MS Access database ? Simply you can add the access DB as a file to installer project and can be easily deployed in client machines. — Kurubaran
If you're using a .mdf file, or LocalDB - then you're using SQL Server (the full version) and thus you MUST install the SQL Server software on the client - possibly the Express edition or the further slimmed down LocalDB version - but still, you have to install something. — marc_s
You want a SQL Database but you don't want to install any software? Perhaps you don't actually need a SQL database, perhaps you can use an XML file. As an aside, LocalDB is de facto at this point, and SQL CE is legacy software. Additionally, LocalDB offers a silent install mode, you can get the localdb.msi installers here microsoft.com/en-us/download/details.aspx?id=29062 — Shaun Wilson

J0e3gan J0e3gan · Accepted Answer · 2014-02-22T05:55:43

You should mostly base your choice on your target platforms and the level of SQL Server functionality you expect.

Target Platforms:

LocalDB does not support older versions of Windows (e.g. Windows XP) or WoW. Toad World's LocalDB overview has a good breakdown of its target-platform restrictions.

SQL Server Compact Edition has very few target-platform restrictions. Check out its download page under "System Requirements" for specifics.

Functionality:

LocalDB better approximates SQL Server Express functionally. SQL Server Compact Edition has many functional limitations (e.g. no support for stored procedures).

MSDN's introductory overview of LocalDB explains many of the important functional differences between LocalDB and SQL Server Compact Edition; and the aforementioned Toad World LocalDB overview explains some of what (not) to expect from LocalDB functionally too.

Redistribution:

Both SQL Server Compact Edition (per the "Additional Information" section of its download page) and LocalDB (per SQL Server forums) are freely redistributable: so you should be okay with either with respect to fine print.

Service based database vs SQL Server Compact vs LocalDB?

2 Answers