What is the difference between SQL Server Compact Edition and SQL Server Express Edition?

Question

Hi,

Can someone please explain what is the main difference between SQL Server Compact Edition and SQL Server Express Edition?

For what purpose I should use this or that one?

Thanks

Answer 1

This blog has everything you need, it will even give you a feature comparison.

If you don't feel like reading, SQL Server Compact is for Mobile/Embeded Apps, and Express edition is just a free scaled down SQL Server standard.

Edit A very important thing to point out as @Rup just stated is that Compact is an in memory database.

Answer 2

Express is a fully functional, albeit size-limited (4GB for 2008/2005, 10GB for 2008 R2) version of sql server. In my experience, it is mostly intended for development. Generally speaking, if you want to deploy your SQL Express database to a hosting environment, you must import to a SQL Server database (e.g., GoDaddy). Most hosting providers will not allow you to attach a sql express database from the app_data folder at run time.

Compact Edition can be deployed as a stand-alone, embedded database for client apps. Note that the CE version has multi-threading issues that prevent it from being deployed to a web application. There's a reference to this on Scott Gu's blog...I'll see if I can find it.

Edit: Here you are. You need to wait for version 4 to deploy to ASP.Net web site:

http://weblogs.asp.net/scottgu/archive/2010/06/30/new-embedded-database-support-with-asp-net.aspx

If you want to deploy an embedded database with a web app, System.Data.SQLite is your man...