The FAQ for the Express products has another interesting point about SQL Server Express
What is the difference between SQL Server 2008 Express and SQL Server Compact Edition?
SQL Server Compact Edition is a lightweight , easy to deploy database system that is ideal for Windows database development. A 2MB footprint that requires no administration makes it an ideal solution for creating most data storage needs. Some of the limitations of SQL Server Compact Edition:
* Does not accept remote connections
* It is not compatible with the design time tools for LINQ to SQL
* Does not support stored procedures
SQL Server Express is designed to be feature-rich and powerful database system. It is an ideal solution for creating client-server Windows applications and Web development. Some of the features of SQL Server Express:
* Develop stored procedures with Visual Basic Express and Visual C# Express
* Full support of design time tools like LINQ to SQL
* A huge data storage of 4GB capacity
SQL Server Express is designed to meet the needs of simple applications. It is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database size.
Trivia - Did you know, while the estimated retail pricing for U.S. and Canadian editions of SQL Server 2005 is $24,999, the Developer Edition which includes all the functionality of Enterprise Edition is $49
Related:
Can VS 2008 editions be installed side-by-side?