Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express.
Feature |
SQL Server Compact 3.5 SP2 |
SQL Server Compact 4.0 |
SQLite, incl SQLite ADO.NET Provider |
SQL Server |
SQL Server 2012 LocalDB |
Deployment/ Installation Features |
|||||
Installation size |
2.5 MB download size |
2.5 MB download size |
10 MB download, 14 MB expanded on disk |
120 MB download size |
32 MB download size |
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.NET |
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 |
140 TB |
10 GB |
10 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 |
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.NET Sync Framework |
Yes |
No |
No |
Yes |
Yes |
LINQ to SQL |
Yes |
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 that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.