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.
Related posts:
Backup All databases using T-SQL
Free SQL Server Ebooks !!!
Delete all User databases with TSQL
SQL Azure Usage Scenarios- Backup all your databases with TSQL
- SSIS 2008 Video tutorials /Training
- Preapring for MCSA SQL Server 2012 ??
- SQL Server 2012 Certification Prep 3/3
- SQL Server 2012 Certification Prep 2/3
- SQL Server 2012 Certification Prep 1/3
