Microsoft SQL Server Compact Edition or SQL CE is a small footprint client-only edition of SQL Server. SQL CE is Microsoft's database solution for mobile and desktop development.

SQL CE is not a managed database, but has managed wrappers for use from managed code. SQL CE features an unmanaged architecture and was not designed specifically for the Microsoft .NET Framework. In fact it was designed for mobile devices and then ported to desktop machines later out of recognition that this was a needed product space not filled by Microsoft.

VistaDB is an excellent alternative to SQL CE (SQL Compact Edition)

VistaDB provides managed programmers a great alternative to SQL CE (Compact Edition). VistaDB has none of the limitations of SQL CE, and runs on more platforms (Mono supports Linux and Mac OS X). CLR Procs, CLR Triggers, Full Text Search, Views, Image (BLOB data) and other missing features in SQL CE are present in VistaDB. If you need an embedded database with XCopy deployment for a managed environment, look no further than VistaDB.

We have heard over and over from users looking for SQL CE embedding tutorials that the process is too complex and just not intuitive. Our process is very simple; add a reference to our assembly and build.

To deploy VistaDB copy ONE DLL for both 32 and 64 bit machines with your databases. You can even embed our DLL within your app using ILMerge and have a zero copy deployment.

SQL CE requires you deploy different editions based upon the platform, and they cannot both be referenced in your application. You are required to target your application to x86 or x64 when binding to SQL CE. Do you want to support and test two versions of your product with two copies of the SQL CE engine? VistaDB only has 1 Assembly for both 32 and 64 bit.

Embedding VistaDB with your application is by far the easiest option available on the market today for .Net developers.

End user can change SQLCE runtime on your app?

Do you want the engine your application is using to be automatically promoted without your app knowing about it? SQLCE will automatically promote all applications using it when a service pack is applied. Take a look at this release note about SQL CE 3.5 SP2 Beta 2.

The existing installations of SQL Server Compact 3.5 or SQL Server Compact 3.5 SP1 on the computer are upgraded to the released version of SQL Server Compact 3.5 SP2 Beta 2 by installing SQL Server Compact 3.5 SP2 Beta 2 using the MSI file.

That means if an end user installs that beta build, your app would be automatically using it, and there are breaking changes in that service pack.

Even using SQL CE Private Deployment can still lead to problems with bindings and conflicts with the unmanaged code. See the SQL CE private folder deployment errors blog post for the workaround (a binding redirect).

Even when making a private deployment of SQL CE the unmanaged dlls have to be registered on the machine to prevent load errors due to locally installed versions.

VistaDB can truly be embedded with your app through ILMerge, or lives in the same directory as your application. You never have to worry about the user changing out the assembly on you post release with a different version.

32 and 64 bit deployment hassles with SQL CE

SQLCE was originally built for mobile devices, but Microsoft opened it up in 3.1 to include desktop support. But the engine is still unmanaged code and requires complex deployment of 32 and 64 bit engines. Look at this note buried in the release notes on deployment.

Due to changes in SQL Server Compact SP2 and additional 64-bit version support, centrally installed and mixed mode environments of 32-bit version of SQL Server Compact 3.5 or 3.5 SP1 and 64-bit version of SQL Server Compact 3.5 SP2 can create what appear to be intermittent problems. To minimize the potential for conflicts, and to enable platform neutral deployment of managed client applications, centrally installing the 64-bit version of SQL Server Compact 3.5 SP2 using the Windows Installer (MSI) file also requires installing the 32-bit version of SQL Server Compact 3.5 SP2 MSI file. For applications that only require native 64-bit, private deployment of the 64-bit version of SQL Server Compact 3.5 SP2 can be utilized.

Yes, in order for you to "safely" deploy the 64 bit version you have to install the 32 bit version also to avoid "intermittent problems". Those two MSI files are over 6 MB in size, and your installer will have to be 64 bit aware to determine what install order to use.

VistaDB has a single assembly you deploy with your app, external users cannot upgrade the version without you knowing about it and there are no issues with mixed mode deployment. Our assembly will run 32 bit if your application is 32 bit, and 64 bit if you are 64 bit. Easy as it gets.

Management Tools

Do you need to deploy a database management tool for your users? SQL CE does not have an xcopy deployable DBA tool for administrating the database. VistaDB ships the GUI DBA tool Data Builder and an unbranded version with full source code (DBA Sample Tool) is available. This allows you to build custom admin tools for your own applications quickly and easily.

SQL CE does integrate with the SQL Management Studio, but which version you use depends upon the runtime engine. Management Studio 2008 is required to view and edit SQL CE 3.5 files. But the full install of SQL Management Studio is far from being lightweight, and is definitely not xcopy deployable. The VistaDB DBA Sample Tool is xcopy deployable and can be customized to only expose the functionality you desire.

Comparison Chart

Feature VistaDB 4 SQL CE 3.5 SP2
Number of files to deploy 0-2 3-11
Deployment size 1,291 KB 2,801 KB
Setup size 1,291 KB 6,676 KB
Number of concurrent connections OS limit 256
Concurrent process connections OS limit  1
Database size limit No limit 4GB
Max CPUs Supported  All 1
100% managed and typesafe (no DLL Imports) Yes
Useable from ASP.NET Yes
LAN multi user support (shared network drive) Yes
SOAP / Web / WCF in IIS service usage Yes
Developed in C# Yes
Mono (Dot Net 2) support (.NET for Linux, OS X, etc.) Yes (Intel)
Single assembly footprint Yes
ASP.NET Shared Hosting Supported Yes
ASP.NET Membership Providers Yes
Can be completely embedded into a managed .EXE or .DLL to create a single file application Yes
APTC (Allow Partially Trusted Callers) Attribute for ASP.NET apps (Medium Trust) Yes
Isolated Storage as database location support Yes
SQL Views Yes
CLR Procs (Managed C# & VB.NET procs) Yes
TSQL Procs and UDFs Yes
UPDATE ... FROM syntax support Yes
Temp tables and table as a variable Yes
Connection Pooling Yes
Binary large object (BLOB/Image) support Yes
.NET 2.0 support Yes Yes
.NET 3.0 / 3.5 support Yes 3.5
.NET 4 Support Yes 3.5 SP2
ASP.NET and Web Service Support Yes
Compact Framework 2.0 support Yes
.NET 64-bit framework native support Yes 3.5
Single assembly for 32 and 64 bit support Yes 6MB Msi required
Visual data management tools (Data Builder) Yes
Visual data migration tools (Data Migration Wizard) Yes
Procedural T-SQL Select, Case and If support Yes
Implicit and explicit, transaction processing Yes
Blowfish encryption Yes
Network shared access (LAN / Shared drive) Yes
UNICODE (NChar, NText, NVarchar) Yes Yes
Windows Forms support (WinForms) Yes Yes
Wide range of data-types, character data types, IMAGE, MONEY, and IDENTITY Yes Yes
Full referential integrity with cascading deletes and updates Yes Yes
Multiple connections for foreground and background operations Yes Yes
Set Functions (aggregates), INNER and OUTER JOIN, subselect, and GROUP BY and HAVING clauses Yes Yes
CLR Triggers (C# and VB.NET) ( INSERT / UPDATE / DELETE) Yes
T-SQL Stored Procs / UDFs Yes
Full Text Search (FTS) indexes Yes
Visual Studio 2005 Server Explorer Integration 3.1
Visual Studio 2008 Server Explorer Integration Yes 3.5
Visual Studio 2010 Server Explorer Integration Yes Yes
Visual Studio 2012 Server Explorer Integration Yes Yes
ADO.NET Entity Framework Support (LINQ to Entities) Yes Yes
DBA tool with source for embedding in your application Yes
Full Database Engine Source Code available Yes

The Microsoft ILMerge utility can be used to fully merge the VistaDB .NET assembly directly with your .EXE or .DLL to make a single-file deployment and eliminate the need to deploy additional files.