There are a lot of different memory cards available. Even just among Secure Digital-format cards, there are SD, SDHC, and SDXC cards of varying sizes, and even in those categories there are varying modifiers like "Extreme," "Ultra," and "Ultimate," all representing a variety of speeds. This can lead to a bit of confusion as to what's hype and what's important.
Basic SD cards can hold up to 2 GB. SDHC cards can hold between 4 GB and 32 GB. SDXC cards can hold 64 GB and more (on paper, the SDXC standard can handle up to 2 TB). While all modern SD card-using cameras can accept SDHC cards, older models might only be able to accept SD cards. An SDHC card simply won't work in a 4 or 5-year-old, SD-only camera. SDXC is a very new standard, and as of the 4.0 specification SDHC-compatible devices can't use them; you'll need an SDXC-specific device, like the Canon Rebel T2i or the Sony Handycam HDR-CX55V.
smaller cameras don't need high-speed memory cards, the amount of information being pushed through while taking a series of high-resolution shots on an SLR requires a faster card. With slower cards, a camera's burst mode might only be able to capture a handful of photos before its buffer fills up and the user has to wait for the files to write to the card.
According to Sandisk, while it doesn't publish transfer rates for its Standard SDHC cards and only describes the 15 MB/sec read speed of its Ultra SDHC cards, its Extreme series of SDHC cards can reach read and write speeds of 30 MB/sec. The company's CompactFlash cards are even faster, with its Extreme CompactFlash cards offering up to 60 MB/sec read/write speeds and its Extreme Pro cards offering up to 90 MB/sec read/write speeds. The Extreme Pro cards are also much, much more expensive than standard CF cards; a 16 GB card retails for over $300, and a 64 GB card costs nearly $900. Because of the higher speeds offered by CF cards, most professional digital cameras like the Canon EOS 1D Mark IV either uses CF or offers both CF and SD card slots.
Don't sweat the memory card speed if you're just taking JPEG snapshots. As long as it's the right type of card (and, at this point, even Sony Cyber-Shot cameras that previously only accepted Memory Sticks can use SD cards), it'll work fine. If you're taking action shots on your SLR, consider upgrading to a faster card, like a SanDisk Ultra SDHC or a Kingston SDHC Class 10 card. If you're planning on professional work with a lot of fast shots and continuous shooting mode, consider investing in a professional-grade card. Otherwise, don't worry about it.