Home Products Compare To... VistaDB vs Microsoft SQL CE 2005

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

Microsoft SQL Server 2005 Compact Edition or SQL CE (SQLCE) and 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 single user desktop development.

Unlike VistaDB 3.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 or Compact Framework.

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 1.2 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. It is that simple. To deploy copy ONE DLL and your databases. You can even embed our DLL within your app using ILMerge and have a zero copy deployment. Embedding VistaDB with you application is by far the easiest option available on the market today.

Visual Studio Integration

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.

SQL CE will not support the LINQ to SQL Driver for Visual Studio 2008, even though it does support SQL Server 2008. SQL CE users will have to wait until the Entity Framework release before they can use LINQ against a SQL CE database.

SQL CE is not for multi user

SQL CE in all of it's versions is not designed for a multi user environment. In fact, it is blocked from doign so. You can open multiple connections to the database from your process, but not from other processes or from remote machines. The database was designed for a single user application.

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
• Cannot be used across a network or with ASP.Net (no multi user support)
• 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 webservices / SOAP
• Maximum usage of 1 CPU
• No native 64 bit version (Version 3.5 is supposed to have a 64 bit runtime)
• No Full Text Search indexes
• No cross platform support

Comparison Chart

Feature	VistaDB 3.3	VistaDB 2.1	SQL CE
# of files to deploy	0 or 1	2 or 3	2-7
Size of total deployment	900 KB	700 KB	1,834 KB
Number of concurrent connections	Unlimited	Unlimited	256
Concurrent process connections	OS Limit	OS Limit	1
Database Size Limit	4 TB	4 GB	4 GB
Max CPUs Supported	All	All	1
100% fully managed and typesafe
Useable from ASP.NET
LAN multi user support
SOAP / Webservice usage
Developed in C#
Mono 1.2.x support (.NET for Linux, OS X, etc.)
Single assembly footprint
ZeroClick™ deployment
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
UPDATE ... FROM syntax support
Connection Pooling
Binary large object (BLOB/Image) support
.NET 1.1 support	(3.0 Only)
.NET 2.0 support
.NET 3.0 / 3.5 support	(3.2)		3.5
ASP.NET and Web Service Support
Compact Framework 2.0 support
.NET 64-bit 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
Implicit and explicit, transaction processing support to commit and rollback grouped changes
Built-in 32-bit to 448-bit Blowfish encryption
Network shared access
UNICODE (NChar, NText, NVarchar)
Windows Forms support (WinForms)
Wide range of data-types, character data types, IMAGE, MONEY, and IDENTITY
SQL-92 compliant
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
T-SQL User Defined Functions (UDFs)
Full-Text Search indexes
Visual Studio 2005 Server Explorer Integration			3.1 Only
Visual Studio 2008 Server Explorer Integration	(3.2)		3.5 Only
LINQ Entity Framework Support	(Q1 2008)		?
Mono Support (Linux and Max OS X)

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.