Differences between using a SQL Server Compact database (.sdf) and SQL Server database

SQL Server Compact database (.sdf):

• max 2 GB file size
• No stored procedures, triggers etc.
• No process but loaded into your AppDomain
• As far as I know, there is no cost based optimizer or query plans for queries
• Lack of concurrent access of multiple users at the same time

The big issue here is, that CE is only a file on your system and you get access through a simple InApp-call using a dll. Thats it and in many scenarios this is enough.

Remember, that you need to deploy the CE-DLL when you wan't to publish your app!
SQL Server Compact doesn't have any in-memory cache, so all queries hit disk all the time. 
SQL Compact doesn't support Stored Procedures. You write all of your query directly in code.

SQL Server database:
With SQL Server Express hot data stays in memory which can speed queries up significantly, especially if the queries are run often

Useful Compare Link:

1. Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB
2. Differences Between SQL Server Compact and SQL Server
3. SQL SERVER – Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition