Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Information about LocalDB comes from here and SQL Server Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the "User Instance" feature in SQL Server Express.

Feature	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl sqlite ADO Provider	SQL Server Express 2012	SQL Server LocalDB
Deployment/installation Features
Installation size	2.5 MB Download size MB expanded on disk	2.5 MB Download size MB expanded on disk	Ten MB download, MB expanded on disk	Download MB Size > MB expanded on disk	MB Download size > MB on disk
ClickOnce deployment	Yes	Yes	Yes	Yes	Yes
Privately installed, embedded, with the application	Yes	Yes	Yes	No	No
Non-admin installation option	Yes	Yes	Yes	No	No
Runs under ASP.	No	Yes	Yes	Yes	Yes
Runs on Windows mobile/windows Phone platform	Yes	No	Yes	No	No
Runs on WinRT (Phone/store Apps)	No	No	Yes	No	No
Runs on Non-microsoft platforms	No	No	Yes	No	No
Installed centrally with an MSI	Yes	Yes	Yes	Yes	Yes
Runs in-process with Application	Yes	Yes	Yes	No	No (as process started by app)
64-bit Support	Yes	Yes	Yes	Yes	Yes
Runs as a service	No–in process with Application	No-in process with Application	No-in process with Application	Yes	No–as launched Process
Data file Features
File format	Single File	Single File	Single File	Multiple files	Multiple files
Data file storage on a network share	No	No	No	No	No
Support for different file extensions	Yes	Yes	Yes	No	No
Database Size Support	4 GB	4 GB	TB	Ten GB	Ten GB
XML Storage	Yes–stored as ntext	Yes-stored as ntext	Yes, stored as text	Yes, native	Yes, native
Binary (BLOB) storage	Yes–stored as Image	Yes-stored as Image	Yes	Yes	Yes
FILESTREAM Support	No	No	No	Yes	No
Code free, document safe, file format	Yes	Yes	Yes	No	No
Programmability
Transact-sql-common Query Features	Yes	Yes	No	Yes	Yes
Procedural t-sql-select case, If, features	No	No	Limited	Yes	Yes
Remote Data Access (RDA)	Yes	No (not supported)	No	No	No
ADO Sync Framework	Yes	No	No	Yes	Yes
LINQ to SQL	Yes	No (not supported)	No	Yes	Yes
ADO. NET Entity Framework 4.1	Yes (no Code first)	Yes	Yes	Yes	Yes
ADO. NET Entity Framework 6	Yes (fully)	Yes (fully)	Yes (Limited)	Yes	Yes
Subscriber for Merge replication	Yes	No	No	Yes	No
Simple transactions	Yes	Yes	Yes	Yes	Yes
Distributed transactions	No	No	No	Yes	Yes
Native XML, Xquery/xpath	No	No	No	Yes	Yes
Stored procedures, views, triggers	No	No	views and triggers	Yes	Yes
role-based Security	No	No	No	Yes	Yes
Number of concurrent connections	256 (100)	256	Unlimited	Unlimited	Unlimited (but only local)

There is also a table here This allows you to determine which Transact-SQL commands, features, and data types are Supporte d by SQL Server Compact 3.5 (which is the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB