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.
| 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.
