Currently I am using CF (Compact Flash) cards in my DSLRs and SD
cards in my P&S camera's.
CF cards are the reference for the pro camera's and older consumer camera's. CF
cards are intrinsically more expensive than SD cards, because they include an
embedded controller, a bit like a disk controller, so that the card can be
interfaced as a virtual disk unit. An SD card is just a piece of memory without
much local intelligence, needing a bit more intelligence at the reader's side.
Does speed matter ? yes and no. Thanks to the buffer size on modern camera's, for normal use (this excludes action and sports photography) you won't notice much difference on the camera. BUT speed does matter (a lot) when copying data to your computer. If I have to copy 2GB from my Toshiba 4GB card to my PC, this will take an hour via a PCMCIA adapter, half an hour via a USB2 interface. An ExtremeIII 2GB card will be copied in less than 10 minutes (a bit more than 5 minutes) via a USB2 interface (it seems that the hard disk speed on the PC is also a limiting factor in that case).
My advice, since prices went down recently, if you don't care about the copy time on the PC, take medium speed cards, if this is on the critical path of your workflow, just go for the fastest cards, ExtremeIII or RiData 150x are my best choices at this moment. Regarding size, 2GB is very good, gives enough space and is not too big (you can have several cards with you to split the risk of having a problem with a card).
Don't forget that cards are not 100% reliable, I have had one corrupted CF card (only one picture lost, luckily) and one unreadable SD card...
If you want more detailed information about memory cards, such as speed in different camera's, the well-known reference is Rob Galbraith' site.
My personal experience: with the Nikon cameras I have had no compatibility problem at all (until now). I have used almost any type of card, from 8MB !!! to 8GB, many different brands (Sandisk, UltraII, ExtremeIII, Kingston, Lexar, Toshiba...)