SQL Server Compact Edition(CE): It’s a lightweight in-process database engine you can run on mobile devices or desktop applications. This version is a subset of SQL Server Express edition with some limited functionality. It doesn’t have stored procedure, views, triggers. You should be using this version if your application only requires to store data that can be easily accessed. And another thing is that in-proc with your application could be a power features because you don’t really need to have your server running and eating memory/CPU while you are NOT running desktop/mobile application means it wakes up when it needed.

SQL Server Express Edition: This version is a server based product that has a rich set of data service features and you need to configure number of components during installation. Express edition has the typical funcationality of a data service engine and also extended to support usage as a local data store.

Even though in new age of Internet, what happen if the internet connection goes down for any reason? Would you afford to have your business stop? Offcourse NOT. So having a local offline data make sense in this scenario. So user can work on offline data and when there is a internet connectivity available it will sync up data with central database. You can think off how OUTLOOK works! You can still write your email if you are NOT connected to internet. Microsoft offers two products SQL Server compact Edition and SQL Server Express Edition for local storage. Both editions are FREE to download and deploy.

Here is the comparision what features supported in which version.