SQL Server Compact Edition (SSCE), previously limited to running on Windows CE and Windows Mobile, can now be used on desktop versions of Windows as well. SSCE, also known by the code-name SQL Server Everywhere, can be built into a desktop application, without requiring a server-based instance of SQL Server, and it uses the same database engine and management tools as other editions of SQL Server. However, developers should consider whether the ease of deployment is outweighed by a limited set of features.

From Jet, to SQL Express, to SQL CE

Virtually every application, whether developed for internal use by a corporate IT department or for resale by an ISV, needs to store, update, query, and manage data. Historically, Microsoft has offered two types of products: database services and embedded databases. Database services, such as SQL Server, run as an OS service, handle multiple applications and users simultaneously, and can run on a separate machine from the application. Embedded databases, such as Microsoft's Jet (used by Access, for example), run in the same process as an application and handle requests only from the hosting application.

In recent years, Microsoft has been downplaying Jet and encouraging developers to migrate to SQL Server, leaving developers looking for a small, embeddable database with no Microsoft product that meets their needs. Microsoft has made several attempts at producing a downsized version of SQL Server for these developers, including MSDE (variously called the Microsoft Data Engine and Microsoft SQL Desktop Edition) and its replacement, SQL Server Express Edition. However, they were still database services downsized to fit on a desktop rather than an embedded database.

SSCE fills that gap by providing an embedded database built on Microsoft's strategic database engine.

Easier to Configure, Smaller, and Secure

SSCE shares a number of important technologies with its larger counterparts, including a common query language, access to data via ADO.NET, integration with the Visual Studio development environment, and management by SQL Server 2005 Management Studio. Unlike its larger brethren, which are installed separately from an application and run as an OS service, SSCE is an embedded database: it is deployed with and runs in the same process as its application and serves only that application.

Because it is embedded, SSCE offers advantages over other downscaled versions of SQL Server:

Simplified deployment. The SSCE database engine and files are incorporated into an application. It requires no OS service to be installed or configured and installation does not interfere with other installed applications or database engines. Similarly, SSCE is removed when the application is uninstalled, with no chance of damaging other applications or leaving pieces of itself behind in either the file system or the Registry.

Much smaller. At 1.6MB installed, SSCE is significantly smaller than any other edition of SQL Server. SQL Server Express, for example, requires nearly 200MB of disk space.

Differences from Full SQL

However, developers need to consider important differences when choosing between SSCE and alternatives such as SQL Server 2005 or SQL Server 2005 Express.

Not a client-server, multiuser database. The biggest difference is that SSCE is a local, in-process database, and it does not service requests from other applications or other computers. It can, however, synchronize data with servers running the full SQL Server.

No in-database programming. Although SSCE and SQL Server use the same Transact SQL (T-SQL) language for creating stored procedures, SQL Server allows developers to create stored procedures that encapsulate complex data logic directly in the database and SSCE does not. Similarly, SSCE does not support triggers or views. Microsoft maintains that stored procedures and views aren't worth the additional size that would be necessary to support them, but triggers could be in a future release.

Not full T-SQL. SSCE supports commonly used query features of the T-SQL language, such as the SELECT and INSERT statements, but it doesn't support the procedural language features, such as the IF or SELECT CASE statements.

No distributed transactions. Although SSCE supports transactions (guaranteeing the integrity of the database by ensuring that a series of database updates either succeed or fail as a group), it does not support distributed transactions—the ability to have one database coordinate its activities with another.

Limited XML support. SSCE allows developers to store XML data within the database, but it is roughly equivalent to the level of support offered by SQL Server 2000. Unlike SQL Server 2005, which includes a specialized XML data type along with support for XQuery (an XML-based query language), SSCE treats XML data as simple text.

Resources

The SSCE home page is www.microsoft.com/sql/editions/compact/default.mspx.

The SSCE team maintains a blog at blogs.msdn.com/sqlservercompact/.

SSCE can be downloaded from www.microsoft.com/sql/editions/compact/downloads.mspx.

A whitepaper comparing Compact and Express Editions of SQL Server can be found at www.microsoft.com/sql/editions/compact/sscecomparison.mspx.