Socket 754 is a relatively dead breed. They were the first Athlon 64 processors and are really no match for Socket 939 processors. The Semprons and Sempron 64s still use Socket 754, although they have now migrated to Socket AM2.
Socket 939 is the mainstream Athlon 64 market. Or atleast, it will stay mainstream for 2 more days. I'll get into that later, but basically, Socket 939 brought in Dual Channel memory compatibility (which S754 lacked). Also, S939 processors are slightly more advanced as far as instruction sets go, and they're more efficient with their voltages and temperatures. Socket 939 also had introduced into it, the Socket 939 Opteron series. These Opterons have taken the place of many Athlon 64s, seeing as they're in the same socket. They also overclock like no tomorrow.
Socket 940 is exclusively for Opterons. There are few (though crucial) differences between the S939 and S940 Opterons. Pretty much, the differences in the Opterons allow the S939 Opterons to be more desktop-friendly. But S940 is used by all the major businesses that use Opterons. Also, S940 is the only socket that supports the Opteron 2xx and 8xx processors; S939 does not have support for them.
Socket AM2. Here's a socket you didn't really ask for, but I thought I'd introduce it anyways. This socket has 940 pins, though it IS NOT compatible with Socket 940. You have to remember that this socket is not S940, it simply has 940 pins, but in a different configuration. S-AM2 comes out on May 23rd. This socket pretty much just adds DDR2 memory support to the Athlon 64 processors. DDR2 memory support would theoretically improve the performance, however, there have been little to no advantages. Many people, including myself, have criticized AMD for this socket as there are no apparent advantages to it, and it just serves to alienate all Socket 939 systems. There will be no more support for S939 systems after 2006, due to Socket AM2.
Hope that helped.