Now that SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2008 R2. 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.
Feature | SQL Server Compact 4.0 | SQL Server Express 2008 R2 |
| Deployment/Installation Features | ||
| Installation size | 2.5 MB download size 37 MB expanded on disk | 74 MB download size > 300 MB expanded on disk |
| ClickOnce deployment | Yes | Yes |
| Privately installed, embedded, with the application | Yes | No |
| Non-admin installation option | Yes | No |
| Runs on Windows Mobile platform | No | No |
| Installed centrally with an MSI | Yes | Yes |
| Runs in-process with application | Yes | No |
| 64-bit support | Yes | Yes |
| Runs as a service | No - In process with the application | Yes |
| Data file features | ||
| File format | Single file | Multiple files |
| Data file storage on a network share | No | No |
| Support for different file extensions | Yes | No |
| Database size support | 4 GB | 10 GB |
| XML storage | Yes - stored as ntext | Yes |
| Binary (BLOB) storage | Yes - stored as image | Yes |
| FILESTREAM support | No | Yes |
| Code free, document safe, file format | Yes | No |
| Programmability | ||
| Transact-SQL - Common Query Features | Yes | Yes |
| Procedural T-SQL - Select Case, If, features | No | Yes |
| Remote Data Access (RDA) | No | No |
(more...)
Read more: Everything SQL Server Compact
Read more: Everything SQL Server Compact