Which Free Microsoft Database for Single-User Desktop Applications:

SQL Server Compact Edition or SQL Express ?

Summary

This article compares SQL Server Compact Edition and SQL Express for a particular purpose: the single-user desktop application. The author focuses on two areas of contrast between these two database options: the footprint or resources that an installation consumes on the desktop, and the level of complexity in the features offered by each platform.

Single-User Desktop Database Applications

What is a single-user desktop database application? A "contacts list" is a common example - an application which allows the user to store, browse and retrieve customer details. Another example is a sales rep "on the road" who enters order details on a laptop, which are later synchronized to a central database server back in the office. In truth, web applications are replacing much of the need for the classic desktop database applications - but until we live in a world where connectivity is "always on", there will be a need for local independent database applications.

There was a time when there wasn't much of a question of which small database to use in the Windows world - Microsoft Access was by far the most widely-adopted platform. Access is a completely different database technology to Microsoft SQL Server, the enterprise database platform, and is much less powerful. In recent years the software giant has released multiple versions of SQL Server for different purposes.

Two of the versions are licensed for free to developers and may be deployed in commercial applications: SQL Express and SQL Server Compact Edition. So which one should be used? The answer of course depends on the the particular circumstances.

SQL Server Compact Edition

Prior to version 3.1 of SQL Server Compact Edition, this particular database server could not be used on a desktop computer without having either the full-blown SQL Server 2005 or Visual Studio 2005 also installed. Developers might wonder what was the point (unless the targeted application users were also Microsoft developers), but actually this version was originally aimed purely at mobile devices such as smart phones. Of course, mobile devices were excluded from the restriction of having SQL Server or Visual Studio installed.

With version 3.1, Microsoft has unchained the shackles for desktop usage by removing the restriction of SQL Server/Visual Studio. This makes it worth looking at.

The first big advantage that SQL Server Compact Edition has over SQL Express is the lightweight footprint of 2 MB that it places on the installed platform. This compares very favourably with SQL Express footprint of a 36MB download and whopping 185MB when installed on the desktop.

The second advantage is that it is possible to set up the Compact Edition to install without users needing administrator privileges on their PC. This may not be so important if your target users will be using your application on their home PC. In a corporate environment, however, installing your application with administrator privileges may need intervention from I.T. support - as many enterprises don't give administrator privileges to their non-I.T. employees for their work PCs. Be aware that by not deploying the database as a Windows Service, this means that Windows Update is not aware of the presence of the database engine in this setup so the developer would have to think about how to ensure the engine was maintained and updated with patches.

And the drawbacks? They are a consequence of that small footprint of 2 MB: Compact Edition lacks the power and feature of SQL Express.

SQL Express

Unlike Compact Edtion, SQL Express is genuinely the little brother of the full-blown SQL Server. It comes with the features of the enterprise edition such as stored procedures, transactions, and the ability to implement a complex security model.

You won't be surprised to learn that with the inclusion of features comes a larger footprint. If you provide your application as an internet download, be aware that even the compressed version is 36 MB. And then, once it's installed it takes up 185 MB on the desktop.

Unlike Compact Edition, SQL Express must be installed as a windows service. Developers will make this process transparent to the user by incorporating it in the application installation routine, but it does mean that whoever is installing the application must have administrative privileges on the PC or laptop.

The additional features of SQL Express are there to support multi-user databases, running on a central server and accessed by remote clients. So if you're developing a single-user database, you don't need any of this stuff?

Well, that depends on the complexity of the application and how much logic is required at the data level. With any degree of complexity in the data processing, I recommend looking for a database that supports stored procedures, a feature that encourage scaleable and maintainable database design.

Summary

This article compared and contrasted SQL Server Compact Edition and SQL Express for a particular purpose: the single-user desktop application. Many small database applications will suit Compact Edition, and the author recommends its usage due to the small footprint it leaves on the desktop. The exception is a requirement for complex data logic, which would benefit from the features of SQL Express such as stored procedures and transactions.

Submitted: 25 June 2007

Author: Margaret Cruise O'Brien

(c) M.C.O.B. Technology 2007