SD is an enhanced version of MMC that features encryption, faster transfer, and a little read-only tab on the disk. The thing is the size of a postage stamp, and was made popular by the Palm Pilot, and various ultra-compact digital cameras. Smart Media is currently the most popular choice among mp3 players, but much like SD/MMC/CF, it can be found in all different types of devices also. Compact flash is the memory of choice for most digital cameras due to it being the first to be released, and also it's large size makes it easy to manufacture (ie cheaper), and easier to expand.
CF has reached capacities of 1gb, but expect to be paying one heck of a premium for that capacity. SD/MMC are moving into the 256 MB territory, but since this format is so overpriced, expect to pay about the same amount of money for that capacity as the price of the digital camera. SM is about equivalent to CF in terms of price.