The Personal Edition is for personal workstations or small workgroup servers. Although all SQL Server editions provide essentially the same core database kernel, supported features will vary from one edition of SQL Server to another. The Personal Edition doesn't give you access to most high-end features, such as distributed partitioned views, and certain parallel query techniques. The Personal Edition is probably the most confusing SQL Server version because you can't buy it. According to the End User License Agreement (EULA), the Personal Edition is part of the Client Access License (CAL) and client software. If you have Standard or Enterprise Edition licenses, you get Personal Edition for free, but you can't buy it as a separate product. This edition makes sense if you're running Windows 2000 Professional or Windows 98 and you need access to the GUI administration tools. (The Standard and Enterprise editions don't run on Win2K Pro or Win98, and no SQL Server 2000 edition supports Win95.) But don't use the Personal Edition if performance is absolutely critical; a concurrent workload governor, tuned for five concurrent users, throttles this version.
The Desktop Engine was formerly known as Microsoft Data Engine (MSDE). Like MSDE, the Desktop Engine can be freely distributed (as long as you follow the EULA rules) and doesn't include administration tools. This SQL Server 2000 edition makes sense if you need a low-volume data store, and it provides great support for disconnected corporate road warriors. Microsoft confused the market when it released MSDE in two subtly different versions bundled in Microsoft Office and Visual Studio. Fortunately, the company has done away with that model; the Desktop Engine comes in only one version. But you can still get confused by the product names. SQL Server 7.0 Desktop edition is comparable to what Microsoft now calls the Personal Edition. And don't confuse the SQL Server 7.0 Desktop Edition with the SQL Server 2000 Desktop Engine; they are different products.
The Standard Edition is the workhorse that most SQL Server users will want to buy. This edition supports up to four CPUs and 2GB of RAM but still lacks some of the Enterprise Edition's advanced performance features such as distributed partitioned views and indexed views. This edition also doesn't support Microsoft Cluster Server (MCS), log shipping, and certain advanced OLAP features, such as native HTTP. In addition, you must purchase separate licenses for each Standard Edition instance you install on a machine.
The Enterprise Edition includes all the SQL Server 2000 bells and whistles; it's also four times more expensive than the Standard Edition (assuming retail cost for per-processor licensing). The Enterprise Edition gives you access to all of SQL Server 2000's advanced tuning and fail-over capabilities, and you can install as many instances as you like on the same server (although Microsoft has tested only 16 instances per physical server). The Standard Edition will probably give you all the performance you need, but many of you (especially heavy Analysis Services users) will find that you simply can't live without certain Enterprise Edition features, such as clustering, log shipping, and HTTP access for OLAP cubes. Be careful when you compare costs. Initially, you might think that the Standard Edition meets your needs, but the Standard Edition might be missing that one killer Enterprise Edition feature that your application absolutely must have. Still, quadrupling your database cost could play havoc with your project budget.
The Developer Edition is functionally equivalent to the Enterprise Edition but is licensed for use only as a development platform. Fortunately, Microsoft got rid of those incredibly annoying problems that Microsoft Developer Network (MSDN) users sometimes ran into. I hope this short description clears up some of your questions. See the following URLs for more information.
The SQL Server 2000 Books Online (BOL) article "Features Supported by the Editions of SQL Server 2000" also contains useful information. Finally, you should also read the EULA in your installation media's license.txt file. The EULA won't tell you which SQL Server 2000 edition contains which features, but it is the legal binding document that spells out exactly how you can use the SQL Server license you purchased.