SQL Server 2005 Compact Edition: The Little Engine That Can

Microsoft SQL Server 2005 Compact Edition (SQLce) is the newest version of Microsoft’s mobile database solution. SQLce provides relational database functionality for mobile and desktop applications through a lightweight and easily distributable engine. Applications that need relational database capabilities but are not connected or only sometimes connected to a network can benefit greatly from SQLce.

Although currently still in beta, SQLce is available for download.

A Bit of History

SQLce started life as SQL Server Mobile. The new version initially was going to be named SQL Server 2005 Everywhere Edition, but during the testing phase several Microsoft customers voiced concerns about the name being confused with other database systems. Therefore, Microsoft elected to officially change the product name to SQL Server 2005 Compact Edition.

Because of the mid-stream name change during development, the literature, documentation, and code use references to all three names. Microsoft plans to clean all of this up before the official release becomes available and use SQL Server 2005 Compact Edition throughout.

What Can You Do for Me?

Although SQLce is billed as a lightweight relational database engine, it is not light on features. A single article could not hope to cover the full breadth of SQLce, so I am going to examine the features of SQLce that I feel are most important:

  • Database features
  • Management and deployment
  • Programmability

Database features

Although SQLce does not provide full SQL Server functionality, it is still a very capable database engine. These are some of its key features:

  • Single data file: An entire database is encapsulated within a single file. This allows easy installation of applications and databases by simply copying the files from one location to another. There is no exporting/copying/importing or detaching/copying/attaching required to move or copy a database.
  • Encrypted data: For protection of your database, you can encrypt the entire database file using a password and 128-bit RSA file-based encryption. The password is used to encrypt the database, not just control access. So, your data is safe even if it falls into the wrongs hands. Just be careful: If you lose the password, there is no way to recover the data in the database file.
  • Databases up to 4GB: Individual database files are limited to 4GB, but you can have as many database files as you need. You might be thinking that 4GB is not as much as it used to be, and you would not be alone in that thought. I have seen several complaints on various blogs and forums from users and developers about this arbitrary size limit. I have seen some unofficial responses from Microsoft about enlarging or possibly removing the 4GB limit. This is speculation on my part, but I would not be surprised to see SQLce released with the 4GB limit and then a service pack or future release released with it removed.
  • Full referential integrity: Maintaining the integrity and accuracy of your data is vital. SQLce simplifies data management by providing full referential integrity, including support for cascading deletes and updates.
  • Transaction support: In conjunction with referential integrity, transaction support is another key feature in protecting data accuracy. SQLce protects data by providing complete atomicity, consistency, isolation, and durability (ACID) support.

Management and deployment

Management and deployment of application databases is often a headache for developers and system administrators. The following SQLce features simplify management and deployment of the engine and the database:

  • Small engine footprint: The SQLce engine consumes only 2MB of disk space and approximately 5MB of memory when in use. Its small size makes SQLce suitable for use on all types of devices, from Pocket PCs to full Windows desktop systems.
  • SQL Server 2005 Management Studio integration: SQLce databases can be fully managed via the SQL Server 2005 Management Studio or the Visual Studio 2005 IDE. By leveraging familiar interfaces, developers and administrators can quickly begin utilizing SQLce.
  • Ease of installation: SQLce can easily be deployed by using the ClickOnce deployment option. Administrators will appreciate ClickOnce deployments because deploying or updating an application requires only updating files on a server. If a ClickOnce deployment is not an option, SQLce can be deployed just as easily by copying the database engine, which is a set of DLL files, into your application folder.

Programmability

SQL Server 2005 Compact Edition provides developers with many productivity enhancements. The following features are some that are particularly interesting:

  • Integration with Visual Studio 2005: SQLce provides tight integration with both Visual Studio 2005 and the .NET Framework. This allows developers to leverage their existing knowledge and quickly begin developing applications with Visual Studio 2005 and SQLce.
  • Code reuse: Because of the compact size of SQLce and the tight integration with the .NET Framework, developers can create code that can be used to build applications for multiple devices, including Pocket PCs, Tablet PCs, and Windows desktops and servers.
  • Familiar SQL syntax and ADO.NET: Although SQLce provides only a subset of the T-SQL query language available in the larger versions of SQL Server, it does provide many important query language features. Aggregate functions, INNER and OUTER JOIN, subselects, and GROUP BY and HAVING clauses are all supported in SQLce. It also uses the ADO.NET programming model for data access, so developers who are already familiar with .NET and data access will be able to utilize SQLce very quickly.

Say Hello to Your Little Friend

SQLce is an intriguing database engine that any application developer in need of a lightweight database on the desktop or in mobile devices should check out. The combination of a small footprint, .NET Framework integration, and ease of installation make SQLce a great choice. This article described several of its features but barely scratched the surface of all the possibilities. Download the beta and give it a try.

About the Author

Josh Fitzgerald is an applications development group leader for a large medical device company in Warsaw, Indiana. Designing and developing Visual Basic .NET applications is only one of his responsibilities, but it is his favorite part of his job. You can reach Josh at josh.fitzgerald@gmail.com.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read