I believe only Professional and above may create MSSQL CE databases.
SQL CE is installed with 2008 studio, I was surprised to see it myself and didn't see an option to exclude it.
Personally I use VS 2008 to add items to my project databases. Since it's open anyway I just use it. It provides most of the same functionality as Management Studio. If I am doing separate database work, I usually do it in Management studio.
The CE databases are pretty useless. They have a limited sub-set of types and functions. They do not support views or stored procedures. Basically they only store tables; I haven't attempted to create foreign keys in them.
There is no way to rename tables or columns. You must create a new table.
Once you create your database it must be manually added to your project.
You cannot directly create a LINQ to SQL class in VS2008 on a CE database. You need to use the command line tool sqlmetal (called from .NET console) to create the class for you.
I've had issues where the Class created for a CE database didn't work like a class built for a regular SQL database. Because of this I abandoned using a CE database all together.
It would be nice if there was a reliable way to use Linq to SQL on SQLite databases. SQLite is a great self-contained database not requiring any type of external engine.
Sorry I don't have any performance statistics for an SQL CE database.
I hope this is somewhat helpful.
Brett
You can create .sdf files with SQL Server 2008 Management Studio.
In the File > Connect Object Explorer dialog,
change the Server Type dropdown to SQL Server Compact Edition.
Then in the Database File dropdown choose New Database.... and you're ready to go.
Best Answer
You cannot use SSMS to open SQL CE 4.0 Database files.
You can use the Microsoft Web Platform Installer to load VS Tools for SQL CE 4 however.
source: http://social.msdn.microsoft.com/Forums/en/sqlce/thread/a9fca12d-6403-47ff-ab6b-fc1db42205c3
WebMatrix should be able to view/edit these database files as well.