I often received question regarding what are difference between SQL Server Compact Edition (CE) and SQL Server Express Edition. In one line – SQL Server CE is for mobile application and embaded systems where as SQL Server Express Edition is limited feature light version of SQL Server Standard.
| SQL Server Compact Edition | SQL Server Express Edition |
| ClickOnce Deployment | ClickOnce Deployment |
| Installed centrally with an MSI | Installed centrally with an MSI |
| XML storage | XML storage |
| Transact-SQL | Transact-SQL |
| Subscriber for merge replication | Subscriber for merge replication |
| Simple transactions | Simple transactions |
| Database size support – 4GB | Database size support – 4GB |
| Number of concurrent connections – 256 | Number of concurrent connections – Unlimited |
| Privately installed, embedded, with the application | |
| Non-admin installation option | |
| Runs on Windows Mobile platform | |
| Runs in-process with application | |
| Data file storage on a network share | |
| Support for different file extensions | |
| Code free, document safe, file format | |
| Remote Data Access (RDA) | |
| ADO.NET Sync Framework | |
| Subscriber for merge replication | |
| Simple transactions | |
| Runs as a service | |
| Procedural T-SQL | |
| Distributed transactions | |
| Native XML, XQuery/QPath | |
| Stored procedures, views, triggers | |
| Role-based security |
I am interested to know if my readers have used any of these servers what was the real life application for the same.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
We use SQL Server Express for as the database for our distributed logistics software. Since we do our work on a contract basis, you can’t beat it in terms of lowering our overall fee (no licensing) and security.
I’ve used SQL Compact for a couple projects so far. I love it for desktop apps (with no server component) because it is so simple to deploy. I’ve also used it in conjunction with Sync for ADO.NET to develop an “occasionally connected” application. It and the sync framework work really well.
One thing to be aware of is that SQL Compact only supports a subset of SQL Server data types and there is little in the way of programmability – that is, no stored procedures, udfs or triggers.
I want to use SQL Server Compact Edition for windows mobile 6.0.
Can you please tell me it is free of cost like access databsase or i have to purchase licence of it?
Can I use SQL Server Compact Edition in Client Server environment?
Comprehensive answer
You have saved my time
Thanks
4 gb is for a single database? is there any limitation for number of databases?
Have you got a decent reply or info about your question somewhere so far? If yes please share with me. I really wonder to learn that like you.
Pingback: SQL SERVER – Download Microsoft SQL Server Compact 4.0 SP1 « SQL Server Journey with SQL Authority
sql campact server is running in any version SQL Server Compact Edition?
thanks for your guide .
is there any version problem?
when i was deploy .mdf file it’s not work in many computer.it’s make a message like that “sql server version 612 support 650 upper ”
please can you tell me is support any version of sql server 2005 or 2008.
>Data file storage on a network share
Afaik ms sql ce doesn’t support data file storage on a network share.
http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/d983dbdf-6be9-4f11-b76f-5a0ada198363/