Sun. Sep 22nd, 2019

Samir Daoudi's Technical Blog

SharePoint, Office 365, C#, .Net, SQL Server News, Tips, Ebooks, Events, Webinars ….etc

Differences Between SQL Server Compact and SQL Server

SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQL Server SQL Server 2014 LocalDB Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes SQL Server Compact 4.0 SQL Server 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.

Leave a comment

Show Buttons
Hide Buttons
%d bloggers like this: