Being a DBA if someone asks you a question which SQL Server Edition should I go for, well it’s a very tricky questions both for DBA and for the customer as he doesn’t want to shed an extra penny from his pocket.

I’ll start this discussion with SQL 2000, Well MSDE the free version of SQL Server 2000, didn’t include management tools and had a performance governor that made the product unsuitable for all but the most trivial of real-world needs or personal use
Well MSDE is the Microsoft Data Engine and is a scaled down version of Microsoft SQL Server 2000 intended primarily for development use or as a backend database for small office systems. While intended to have a lighter load on system resources than an actual installation of Microsoft SQL Server 2000, it is an otherwise functionally identical SQL database engine to its larger sibling. It is limited to a 2GB database, only supports servers up to 2GB RAM and two processors, and doesn’t support advanced SQL Server features such as BI, RS, Full Text Indexing/Search. The biggest disadvantage of MSDE is the fact that it does not include a GUI management tool and incorporates a performance governor. However if you have purchased a license for SQL Server 2000, then you can use the Enterprise Manager tools included with that to manage your MSDE.

The most significant issue with using MSDE is potentially the performance governor. This artificially limits the performance of the database engine by throttling the number of queries it can process at simultaneously as a result of which it is not suggested not to use MSDE with live production website. Hence the product is unsuitable for all but the most trivial of real-world needs or personal use.

If we talk about SQL Server 2005, then we have SQL Server Express Edition available free of cost. Moreover the SQL Server 2005 Express Service Pack 1 (SP1) now offers a useful set of GUI administration tools. Running SQL Server 2005 Express on a fast dual-core server with 1GB of memory will give you a remarkably capable platform for lower-end production uses. SQL Server 2005 Express with Advanced Services even includes support for Reporting Services.

For SQL Server 2005 and 2008 the free version SQL Server Express Edition can legally be used on laptops that connect with a paid version (such as Standard Edition) on the server, as long as you don’t mind the following limitations of SQL Server Express Edition like database size limit for SQL Server Express Edition: 4 GB for 2005 version or 10 GB for 2008

There is also SQL server workgroup edition and It is mostly used as a data management solution for small organizations that need a database with no limits on size or number of users. Workgroup Edition can serve as a front-end Web server or for departmental or branch office operations. It includes the core database features of the SQL Server product line and is easy to upgrade to Standard or Enterprise Edition.

SQL server 2005 workgroup has Management Studio, Import/Export, Limited Replication Publishing and Backup Log Shipping; and for all this you have to shed around 4000$\Processor or around 730$\5 users.

If you want to use SQL Server for developers then the other option for database developers is SQL Server 2008 Developer Edition. Unlike Express Edition, Developer Edition has no limitations on database size, performance characteristics or functionality. However, it’s only licensed for use by a single developer in a non-production development environment.

Moreover you may use it for your own use in developing applications, but you may not allow others to access either the database or those applications. Before you put the applications into production, you must transfer the database to a fully licensed version of SQL Server

Now we talk about the 2 primary editions for SQL Server: Standard and Enterprise. Standard edition provides basic SQL Server features and will serve the needs of most businesses. Enterprise edition offers additional functionality and a much heftier price tag.

If you are working on Large volumes of data and Working with terabyte-class databases or billion-row tables and need functionality likes offers table partitioning, parallel indexing, and indexed views that can significantly improve performance in large environments then you must choose Enterprise Edition.

Moreover if You are working in a high-availability, mission-critical environment in which you want system to be running a 24/7/365 then features like Online indexing will allow you to maintain indexes without scheduling down-time, and Online Restore and Fast Recovery are both options you want when recovering from a failure.

Enterprise Edition is the only Edition that allows you to replicate data from Oracle.

Enterprise Edition also provide some more advanced BI features, such as parallel processing, cube partitioning, and text mining, hence if you are working with large BI solutions then Enterprise Edition is suitable for you.

So if you have a limited budget then SQL Server Standard is significantly less expensive than Enterprise when purchased on licensed per CPU. The conclusion, If you do not fall into any of these categories, then Standard edition will most likely suffice.

Some of the Myths come in mind while choosing and edition.

Standard isn’t as stable as Enterprise.
Not true. Standard and Enterprise both use the same core; the only difference is the additional features that Enterprise provides.
My other database servers are Enterprise, so all of my new servers need to be Enterprise, too.
Not true. While having a homogeneous environment does simplify many things (planning, maintenance, etc.), you can easily mix and match SQL Server editions within an environment to best meet your needs (and budget!).
I need Enterprise edition in order to replicate partitioned tables.
Not true. Replication can exist between partitioned and non-partitioned tables. In fact, replication by default does not create partitioning schemas on the subscription database, so the destination table is not partitioned unless explicitly requested. That said, if the source tables are partitioned, there may be good cause for the destination tables to be partitioned too, and Enterprise edition should be seriously considered.
I need Enterprise edition in order to cluster.
Not true starting in SQL Server 2005. Both 2005 and 2008 Standard editions support 2 node clusters. In a lot of enterprises, you will see Enterprise installed for clustering only. If you have a two node active/passive cluster and that is your only reason: you are probably overpaying and could be fine with standard edition.
I need Enterprise edition to access more memory
Not true with 2005 or 2008. These versions of SQL will address as much memory as the OS allows in Standard or Enterprise edition.