Do you know the difference between SQL Server Compact edition and SQL Server Express edition?
Which one would you prefer SQL Server Compact Edition or SQL Server Express Edition?
Here it is if you don’t have an idea about that…
Remember that SQL Server express is not the smallest version of SQL server edition… they got SQL server compact edition that is smaller than sql server express.
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.
|Feature||SQL Server Compact Edition||SQL Server Express Edition|
|Privately installed, embedded, with the application||Yes||No|
|Non-admin installation option||Yes||No|
|Runs on Windows Mobile platform||Yes||No|
|Installed centrally with an MSI||Yes||Yes|
|Runs in-process with application||Yes||No|
|Runs as a service||No||Yes|
|File format||Single file||Multiple files|
|Data file storage on a network share||Yes||No|
|Support for different file extensions||Yes||No|
|Database size support||4GB||4GB|
|XML storage||Yes (stored as nText)||Yes|
Common Query Features
|Procedural T-SQLSelect Case, If, features||No||Yes|
|Remote Data Access (RDA)||Yes||No|
|Stored procedures, views, triggers||No||Yes|
|Number of concurrent connections||256||Unlimited|
Hope this helps you!