Differences Between SQL Server Compact and SQL Server

Deployment/ Installation Features	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
Installation size	2.5 MB download size 12 MB expanded on disk	2.5 MB download size 18 MB expanded on disk	10 MB download, 14 MB expanded on disk	120 MB download size > 300 MB expanded on disk	32 MB download size > 160 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.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	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
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	Yes	No	No
Programmability	SQL Server Compact 3.5 SP2	SQL Server Compact 4.0	SQLite, incl. ADO.NET Provider	SQL Server Express 2014	SQL Server 2014 LocalDB
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 (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 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.