December 13, 2024

SamTech 365

PowerPlatform, Power Apps, Power Automate, PVA, SharePoint, C#, .Net, SQL, Azure News, Tips ….etc

SQL Server Compact, SQLite, SQL Server Express or LocalDB ??

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB)

I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product.

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.

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 (not supported) 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)

Source : erikej