I'm a bit shocked at how little SqlServer CE delivers, compared to its competition (cf VistaDB, or even SQLite).
Maybe they'll improve (and I'll be happy to try to remember to remove entries from below when they do), but so far (version 3.5, Visual Studio 2008), I can't really recommend it much...
Here's why:

• Schema:
  • Can't rename a table once it has been created.
  • Can't rename a column once it has been created.
  • Can't insert a column into a table once it has been created.
    • Note: you can only add a column on to the end of a table.
• Views:
  • That's an easy one...there are none/nada.
• Triggers:
  • There are none.
  • Note that SQLite does.
• Stored Procedures:
  • There are none nor UDF.
  • Note that SQLite doesn't have them either (atleast without dropping out to custom methods) but VistaDB does...
• Schema Types:
  •  
• Client/Server:
  • You can open multiple connections to the database from your machine, but not from remote machines.  In other words, you can't use this db as the backend of a very small website (Sqlite could in a pinch, VistaDB definately could).
  • Another point to consider here is: cannot be used for web services / SOAP
• Visual Studio:
  • Unlike working with MDF's you have to add the SDF file to your project manually.
  • Linq2SQL: can't create a *.dbml for a SqlServer Compact database.
  • Note: you have to have to use the command line SqlMetal.exe file to build the *.dbml for you.
• HUGELY IMPORTANT TO KNOW:
  • Even when you figure out how to make a *.dbml file (see faqs below) Linq to SQL is supported on full framework, but not compact framework.
• Other issues: 
  • Unmanaged and not typesafe Win32 architecture
  • TSQL Syntax support is greatly limited.
  • Does not support smalldatetime, image, xml, varchar(max), nvarchar(max), varbinary, char(c), varchar(n) datatypes
  • Maximum usage of 1 CPU
  • No Full Text Search indexes
  • No cross platform support
  • No xcopy deployment of the data access assembly and database to both Windows and Mobile platforms.  Requires distribution of a binary per mobile target.
  • Database max size is constrained to 128MB by default unless you change your connection string to modify this limit

 For a more complete list of what's missing from SqlServer Compact, check out this list that VistaDB put together to promote why their product is better.

That said (and I know that's a considerable list of cons), there are some pros to the database:

• Understands uniqueidentifier. I know that doesn't seem like such a big deal, but coming from working on SQLite, it is.
• Tight integration into the Microsoft stack. In other words, although I think VistaDB is far better, there's the reality that microsoft is building Visual Studio tools to slowly bring it into the developer flow. It's frustratingly not seamless yet, but you can see they are intending to provide Linq2SQL for it, synchronization, etc. In other words...there's a certain amount of skillset cost savings following the herd -- even if the product is inferior...