I've read quite a few mixed stories on this issue lately, and still haven't quite found a good solution. We can't use the SQL Server Compact Edition because the program that interacts with the DB I need to use only runs with the full-blown SQL Server system and is NOT compatible with the Compact Edition at all.
I may just have to get the Pro edition of VS 2010. Not a big deal (it is almost $500), but I'm sure that I would probably end up using the IDE for other projects as well, thus making its purchase somewhat justifiable.
Compact and Express are not the same. Compact contains a deployable package for your app, so if your app needs its own serverless db or if you're hosting your app using the .NET Compact Edition, SQL Server Compact wins.
Express is the full blown server with the following limitations:
- 1 CPU core
- 4GB of RAM
- 10GB DB limit
Otherwise, it's a full blown install of SQL Server.