SQL Server 2005 Express Edition is a lightweight, redistributable version of SQL Server 2005 that is optimized to provide a data store for small-scale data-driven applications. Per Microsoft, “It is designed for building simple, dynamic applications. SQL Server Express helps developers build robust and reliable applications by providing a powerful database that is also free and easy to use. Too often database systems are overly complex for building simple applications. SQL Server Express and the other Express products reduce this complexity by providing a simple but powerful development environment for building data-driven applications. You can design schemas, add data, and query local databases, all inside the Visual Studio 2005 environment. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.“
One question that comes to mind is how is this different from MSDE? Basically, this is an evolution of MSDE “which continues to make the features and functionality of SQL Server technology widely available. SQL Server Express includes enhancements to setup and manageability to make it easier to use than MSDE. SQL Server Express also utilizes simpler and more scalable performance limits than MSDE, which managed throughput with a workload governor.”
Update: Before you try and install this on a machine with SQL Server 2000 I would recommend reading this post which details some of the issues. If you want to download this, you can do so here.