SQL Server 2005 vs. SQL Server 2005 Express vs. MSDE

The following is an overview of the differences between SQL Server 2005 Express, MSDE, and JET (MS Access) Databases; including a short summary of the differences between Express and "Normal" versions of MS SQL Server 2005. Be aware that the article is "as seen by Microsoft".

For a very comprehensive comparison, see the article on the Microsoft Website entitled "SQL Server 2005 Features Comparison (Including SQL Server 2005 Express) ": http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Introduction

SQL Server Express is a free and easy-to-use database product that is based on SQL Server 2005 technology.SQL Server Express uses the same reliable and high-performance database engine as the other versions of SQL Server 2005. It also uses the same data access APIs such as ADO.NET, SQL Native Client, and T-SQL. In fact, it is differentiated from the rest of the SQL Server 2005 editions by the following:

- Lack of enterprise features support
- Limited to one CPU
- One GB memory limit for the buffer pool
- Databases have a 4 GB maximum size

SQL Agent is not included and therefore scheduling of important maintenance, including backups may be a problem.

Features Not Present in SQL Server Express

Some of the features available in other SQL Editions, but not in SQL Server Express, include the following:

- Availability features such as data mirroring, clustering, etc.
- Full-text search
- SQL Agent
- Reporting Services
- Business Intelligence Platform, such as Notification and Analysis Services
- SQL Management Studio, which is the new GUI tool replacing SQL 2000 Enterprise Manager

Comparison with Other Free Microsoft Databases

Comparison with MSDE

SQL Server Express replaces Microsoft SQL Server Desktop Engine (MSDE) in SQL Server 2005 and provides numerous ease-of-use features that enable it to be used by a nonprofessional developer or hobbyist. MSDE is based on SQL 2000 technology and is recommended for use with Windows 9x platforms, while SQL Server Express is based on SQL Server 2005 technology. SQL Server Express has features such as Application XCopy, Robust Setup UI, CLR support, GUI tools, and Visual Studio Integration that are not present in MSDE. However, some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.

The use of merge modules for deployment has been a servicing problem for MSDE, and this functionality is not available in SQL Server Express. The workload throttle in MSDE was sometimes difficult to understand and use. In SQL Server Express, the throttle is removed and instead the engine uses CPU, RAM, and database size limitations to differentiate it from the other editions. The table below shows the comparison of these products.

SQL Server Express 2005	MSDE 2000
User Instance support	Feature not present
DTS in separate download	DTS runtime present
Easy deployment because of no MDAC	MDAC is part of install
MSI only, good servicing story	MSI and MSM, servicing of MSM hard
Subscriber Replication for Transactional, Merge, and snapshot	Merge/snapshot publication supported in addition to replication subscription
Robust setup UI	Basic setup UI
No agent	Agent present
Supports Windows 2000 SP4, Windows XP SP1, and Windows 2003	Supports Windows 98, Windows Me, Windows 2000, Windows XP, Windows NT4, and Windows 2003
CLR support	No CLR support
GUI tools available	No GUI tools
Database size limit: 4 GB	Database size limit: 2 GB
1 CPU, 1 GB RAM	2 CPU, 2 GB RAM
No throttle	Workload throttle enforced for 5 concurrent workloads
Deep integration with Visual Studio	Basic integration with Visual Studio

Comparison with Jet

SQL Server Express is based on the latest SQL Server 2005 technology, while Jet has been in service pack and maintenance stage for quite some time. All the latest and greatest features, such as CLR integration and XML support, are available only in SQL Server Express. The reliability and scalability story of SQL Server Express is also significantly better than Jet. Applications written to SQL Server Express can easily move to SQL Server Workgroup, Standard, or Enterprise editions, while Jet is more difficult to scale up. SQL Server Express also provides finer-grained security control over its database objects. With the Visual Studio 2005 integration and the User Instance feature, we are bringing the ease of use of Jet to SQL Server.