Major Improvements Have Been Made to SQL Server Compact Edition

| by Jonathan Allen Follow 576 Followers on Jul 02, 2010. Estimated reading time: 1 minute |

In process databases are often preferred for installed applications and small websites. When properly designed they are easy to install and require no maintenance on behalf of the user. Unfortunately the current version of SQL CE has flaws that essentially make both scenarios untenable.

Poor engineering made SQL CE unusable in hosted environments. Microsoft was so concerned about this that they used a combination of licensing restrictions and hard-coded blocks to prevent ASP.NET from even reading a SQL CE database. This not only made it useless for hosting small internal websites, it also prevented interesting scenarios like creating downloadable SQL CE files from a website.

Unlike other embedded databases that simply required shipping the DLLs, SQL CE required administrator access to install. This isn’t much of an issue for home users, but large companies offering ClickOnce deployments aren’t too keen on giving all the users administrator access.

SQL CE 4.0 is supposed to remedy both of these flaws. The work has been done to make it safe for hosted environments and the new version doesn’t require installation. And for users worried about out-growing it there is a migration tool that converts it into a standard SQL Server database.

CE is still a “safe” file format. That means it doesn’t contain any embeddable code such as stored procedures. All access has to be done via inline SQL or an ORM such as Entity Framework. It does however have full ACID compliance including basic transaction support. Advanced techniques such as save points and nested transactions are not supported.

SQL Server CE 4.0 will be released as a beta next week. You can read more about SQL CE on Scott Guthrie’s blog.

Rate this Article

Adoption Stage

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Not so hardcoded block by Francois Ward

The "block" that prevented you to use SQL Server CE in ASP.NET wasn't all that "hard coded". There was a documented application variable you could set in ASP.NET to bypass it.

It was just a "warning: this isn't officially supported. If it blows up, your problem. Do you wish to proceed?" kind of mechanic. And most of the time it worked fine for small installs. Not ideal, sure, but it worked, and was documented.

Re: Not so hardcoded block by Jonathan Allen

Thank you for the correction.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

2 Discuss

Login to InfoQ to interact with what matters most to you.

Recover your password...


Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.


More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.


Stay up-to-date

Set up your notifications and don't miss out on content that matters to you