Difference Between SQL Server Compact Edition and SQL Server Express edition

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
ClickOnce deployment Yes Yes
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
Yes Yes
Procedural T-SQLSelect Case, If, features No Yes
Remote Data Access (RDA) Yes No
Distributed transactions No Yes
Stored procedures, views, triggers No Yes
Role-based security No Yes
Number of concurrent connections 256 Unlimited

Here is the link to download.
SQL Server Express Edition
SQL Server Compact Edition

Hope this helps you!

Posted in Microsoft Technology Tagged with:
One comment on “Difference Between SQL Server Compact Edition and SQL Server Express edition
  1. Rajesh says:

    very good points helped a lot to understand with in a span of time thank you, thank you very much