Compact Flash is best and cheapest. You get larger capacities for less. Largest size-wise of the three major formats. STILL the market leader. You'll need faster cards (ie. 60-80x) to compete with some of the faster SD and MS Pro cards.
SD is 2nd in market share and growing. Possibly the most supported format. It's smaller but like Compact Flash you need something like Panasonic or Sandisk Ultra II to match MS Pro card speeds.
MS Pro. 3rd in market share and growing. Least supported. Sony, Samsung, and maybe a couple of other manufacturers. However has the fastest standardized speeds. Basically when you buy a MS Pro you're guaranteed a fast card. Higher capacity than most SD but more expensive.
Just depends on what you desire. Larger capacity? Faster speed? Smaller size? Compatibility? etc.
Also there's XD, which not well supported, smaller than SD, and smallest capacity. Just Fuji and Olympus (maybe an odd player here and there. Kodak most recently added it.), MMC which is for the most part pin compatible with SD and comparable speed wise, mini-SD a new SD format for portables and cellphones and MS Pro Duo (ditto).
Soon to come: Memory Stick High Speed (fast, read and write, improved temperature operating range , larger capacities) and Pansonic Pro High Speed (really fast, up to 20MB/s).
Sometime in the future: A Taiwanese card format meant to obliterate all others (something like up to 120MB/s and 2 TeraBytes) someday.
On the way out: SmartMedia (predecessor to XD), and Memory Stick (predecessor to Memory Stick Pro, Pro Highspeed, and Pro Duo).
Still around but on the way out: Memory Stick Duo. Some Sony compact cameras and cellphones still use this currently.
