Why you need to know about SQL Server Compact Edition (SQL CE)?

So you are a developer who works on applications that run on the desktop; or you are a project manager who has only heard of SQL Server Compact Edition… In any case, if you have hardly heard of SQL Server CE, or you are assuming that SQL Server CE runs only on portable devices, then you should read on to learn about some new powerful options available for your environment of choice.

Well, SQL Server CE in not only intended for devices who run on the Windows CE platform (Windows Mobile, Xbox 360 and many others among then) but also for PCs running XP and Vista:

WHAT? You didn’t know? Never mind, keep reading then…

While SQL Server comes in many flavors, SQL Server Express is not the smallest of them all. Yes, it’s free and it can run in your desktop if you want it… but do you really want to run a SQL Server on your desktop? Is that the smallest you can go?

While you should use a service whenever appropriate, common sense will indicate you to use something even smaller if the application only requires a store for data that can be easily accessed. Yes, you could access that application data if you store it in plain (or custom) data files, or you could build your own XML data structures, but those are plain ASCII file with either little or no security. If you secure them with encryption services, one of the many available APIs, accessing those files will happen at a much higher cost than your plain ASCII counterparts.

So, This is where SQL Server CE comes along to the rescue. SQL Server CE keeps the name "SQL Server" for consistency’s sake but is smaller than a server. In fact, it does not even run as a standalone service, it runs in-proc with your application only when instantiated and provides many of the existing features in SQL Server while keeping execution cost at a minimum. There are versions of SQL Server CE available for the PC and Windows CE (where it originated), but Microsoft saw a strong potential in this tool for the desktop and pushed for its further development as a clear alternative to a full blown service.

SQL Server CE has a smaller footprint than a regular SQL Server service by having some limitations such as running in-proc with your application, not having stored procedures or views. However, running in-proc could be seen as a powerful feature instead of a limitation, because you don’t really need to have your server running and eating precious CPU cycles off your desktop/mobile device all the time. Instead your SQL CE will behave as if it "wakes up when needed" creating the illusion of an always available, but not always eating processor cycles. Additionally; even though it runs in-proc; you could still have multiple applications within a single computer/device accessing the same SQL CE database, or you could have multiple clients accessing a shared database. Again, please have in mind that this version is intended for use where you would have not used SQL Server and would have used a different type of data store. It can be taken to extremes, but at those extremes, the functionality needed may overlap with other SQL Server flavors and you may want to choose those. In summary: choose wisely.

Many features can be brought up as being pretty cool in SQL Server Compact Edition, however; in my opinion, one of the most important strengths of this tool is the ability to synch back to any other SQL Server. This would enable you to store only the part of the corporate data that is needed as a distributed portion right where your application resides and only on scheduled intervals or whenever triggered have the databases synch up for replicating the data in, out or both ways. You could have a complex merge replication scheme to help your different locations see only what pertains to their location without having to distribute a server to them. This would allow for distributed applications with smartly-distributed databases without the need for server distribution… in corporate terms that could amount to an amazing amount in savings!

If that wasn’t enough; here’s the best part: IT’S FREE! Yes, you read it right, you can download SQL Server Compact Edition for free from right here:

About Diego Samuilov

Editor in Chief/Founder Diego Samuilov is an executive, consultant, IT strategist and book, e-book and web published author. Diego has worked in Microsoft’s environments since 1990. Since then, he has successfully filled many positions related to the Software Development lifecycle. Having worked as a developer, analyst, technical lead, project lead, auditor and, since 1996 a project manager, manager, director and VP in the Software Development, Server, Desktop and Mobile environments. Diego is very passionate about the software development process, which has played a great part in his skills development. Since the introduction of the first ever PDA (the Apple Newton MessagePad) in 1994 and Windows CE in 1998 he has pioneered and pushed the envelope in the field of mobile software development. He has developed many solutions used in mobile markets, desktop and server environments. He participates in public and private developer community events. He actively collaborates with the community at support forums and blogs. Diego is the author of "Windows Phone for Everyone" available [HERE].