VistaDB compared to Microsoft SQL CE 3.1 (SQL Compact Edition, SQL Everywhere and SSEv)

Microsoft SQL Server Compact Edition or SQL CE was previously known as SQL Server Everywhere and SSEv, is a small footprint client-only edition of SQL Server 2005. SQL CE is Microsoft's database solution for mobile and desktop development.

Unlike VistaDB 3, SQL CE is not a managed and typesafe database. 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 space.

Don't just take our word for it.  Read this blog interview with CyberSavvy discussing the pros and cons of deploying various embedded database options and why VistaDB is the option they chose.

VistaDB is an excellent alternative to SQL CE (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 3. 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.

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) to allow 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 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 Views
• No support for Triggers
• No support for Stored Procedures or UDFs
• Cannot be used across a network or with ASP.Net
• TSQL Syntax support is greatly limited
• Does not support smalldatetime, image, xml, varchar(max), nvarchar(max), varbinary, char(c), varchar(n) datatypes
• You cannot integrate CLR assemblies
• Cannot be used for web services / SOAP
• Maximum usage of 1 CPU
• No Full Text Search indexes
• No cross platform support
• No xcopy deployment of the data access assembly and database to both Windows and Mobile platforms.  Requires distribution of a binary per mobile target.
• Database max size is constrained to 128MB by default unless you change your connection string to modify this limit

Comparison Chart

Feature	VistaDB 3	SQL CE
# of files to deploy	0 or 1	2-7
Size of total deployment	1,023 KB	1,834 KB
Number of concurrent connections	Unlimited	256
Concurrent process connections	OS Limit	1
Database Size Limit	4 TB	4 GB
Max CPUs Supported	All	1
100% fully managed and typesafe
Useable from ASP.NET
LAN multi user support
SOAP / Web service usage
Developed in C#
Mono (Dot Net 2) support (.NET for Linux, OS X, etc.)
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 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
ASP.NET and Web Service Support
Compact Framework 2.0 support
.NET 64-bit framework native support		3.5
Visual data management tools (Data Builder 3 includes full C# source)
Visual data migration tools (Data Migration Wizard 3 includes full C# source)
Procedural T-SQL Select, Case and If support
T-SQL WHILE, TRY-CATCH support
Implicit and explicit, transaction processing support to commit and rollback grouped changes
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
Scrollable and updatable cursors that provide fast and easy data access
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 indexes
Visual Studio 2005 Server Explorer Integration		3.1
Visual Studio 2008 Server Explorer Integration		3.5
LINQ Entity Framework Support	(soon)
XCopy deployable management tool for users
Source Code available

The Microsoft ILMerge utility can be used to fully merge the VistaDB .NET assembly (VistaDB.NET20.DLL) 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 new Visual Studio 2008 environment so you can do maintenance on a project, then reinstall when you go back to a new project.  Get VistaDB, one database in Visual Studio 2005 and 2008 without any configuration changes.