VistaDB vs SQL CE (SQL Server Compact Edition)

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.

SQL CE is not for multi user

SQL CE, in all of its versions, is not designed for a multi user environment.  You can open multiple connections to the database from your machine, but not from remote machines.

The database was designed for single user applications, and Microsoft actively discourages its use for multi user scenarios or ASP.NET development.

You can open up to 256 connections from your machine, but if a remote system opens the file you are immediately blocked down to 1 connection as it requires an exclusive lock on the file.  I think is probably to overcome the Windows file sharing performance problems.  This means that SQL Compact Edition cannot be used for most small business LAN shared database scenarios.

SQL CE is not for embedding

SQL CE may not be embedded within your application using ILMERGE, or rebranded.  If you deploy SQL CE to a customer machine there is a possibility that Windows Update will update the engine without your knowledge.

VistaDB allow for a truly embedded .net database engine.  You can rename the database extension and merge our assembly into your application.  Your users don't have to know we exist! 

We also offer the source code to VistaDB to business users for even further customization.  If you want to truly embed a database in your .Net application VistaDB cannot be matched.

SQL Compact Edition (SQL CE) Limitations and Restrictions

• Unmanaged and not typesafe Win32 architecture
• No support for Updatable Views
• No support for Triggers
• No support for CLR Stored Procedures or UDFs
• No support for TSQL Procs or TSQL Functions
• Cannot be used across a network shared drive or with ASP.Net
• TSQL Syntax support is greatly limited
• No TSQL structured exception handling TRY...CATCH
• Does not support smalldatetime, image, xml, varchar(max), nvarchar(max), varbinary, char(c), varchar(n) datatypes
• You cannot integrate CLR assemblies
• Cannot be used inside web services / SOAP / WCF services hosted in IIS
• Maximum usage of 1 CPU
• No Full Text Search indexes
• No cross platform support with Mono
• Database max size is constrained to 128MB by default (change your connection string to modify this limit)

Comparison Chart

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

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.

SQL CE Visual Studio Integration Problems

SQL CE 3.1 only works with Visual Studio 2005, not VS 2008. SQL CE 3.1 is really only a service pack for SQL Everywhere 3.0 - they changed the name, and they let it run on the desktop. Even though it already could run on the desktop, Microsoft had blocked it for marketing reasons.

SQL CE 3.5 GUI designers only work with Visual Studio 2008, although you can manually write code against 3.5 in Visual Studio 2005. The 3.5 runtime does not integrate with Visual Studio at all; you must install the SDK version. And once a 3.1 database is opened in 3.5 you cannot touch it from 3.1 again.

So if you open a project in Visual Studio 2008 that was written in Visual Studio 2005 / SQL CE 3.1 you have now modified that database so you cannot go back to Visual Studio 2005.  Your database has been permanently modified (probably not what you intended).

Take a look at the review written in Visual Studio magazine.  Visual Studio 2008 with 3.5 causes a ton of problems for managing the database.

"The bad news is that there are three solutions. The first and most foolproof is to not install SQL 2008. The second is to use SQL 2008 keeping in mind that once you do, you won't be able to work with any SQL CE 3.1 databases in Visual Studio 2008. The last is a bit more fun: Replace 3.5 with 3.1 in Visual Studio.

We'll close this prose with a step-by-step recipe for making VS 2008 use a SQL CE 3.1 database:

• Remove SQL Server CE 3.5 Design Tools.
• Remove SQL Server CE 3.5.
• Remove SQL Server CE 3.5 for Devices.
• Install SQL Server CE 3.1 SDK.
• Copy files from a 3.1 install with VS 2005 to the appropriate folder in your VS 2008 install.
• Change your references in the project to the new files just moved/installed. "

Probably not what you want to do, wipe your Visual Studio 2008 environment so you can do maintenance on a project, then reinstall when you go back to a new project.