I do some pro photography and never shoot RAW. On the CF cards, for some reason they seem to be phasing out.
SDHC cards are smaller end in the standard size. The micro/mini variants are smaller still. Less space for cards more space for stuff ( even inside of a DSLR) or easier to put in second slot ( in the case of D300 with CF & SD ).
SDHC cards caught up to the capacity and speed that older CF cards had. For newer cameras where the sensor size isn't too substantially larger ( than former top end CF cameras) there is no need. Higher end SD cards are at 6 MB/s so that's 1 frame/sec with no buffer if the sensor has 6MB images.
The newest CF cards are still faster. The SD 3.0 spec allows for top rate around 104 MB (eventually). Memory is also cheaper now so can buffer several shots in memory to offset slower disk somewhat.
For the camera shotting at high frame rates and have to flush the buffer as fast as possible. CF still has an advantage. Likewise on cameras where they keep dramatically cranking up the RAW file size on each iteration they'll stick around.
CF type II is looks like getting dropped even on camera that take CF. So Flash capacity limits are the same for both.
As a small "ATA" hard drive CF works OK. As small-as-possible card, it isn't so hot (or as hot. )