It's the most generally available format, fitting the highest number of
devices, also generally slightly cheaper than CF, since a couple of years
ago. All trends are in SD's favour.
I don't think it's slower than CF, it's certanily fast enough - even for
high quality video.
CF has the weakness that you can bend the pins if you get dirt in the
holes or if you put the card in the wrong way (stress, distraction). This
means a costly repair of the camera, no warranty issue.
Besides all Pentax DSLRs (after the first?), the D40, D40x, D50 and D80
use SD, as do the L1 and L10 and for sure any future Panasonic DSLR.
Some Canon Pro DSLRs also take SD in the second slot. So even within
DSLRs, CF's dominance is modest.
I wouldn't be surprised to see Canon switching to SD for their Rebel line.
Just my two oere
Erik from Sweden
When posting photos for comment, please give basic settings and/or leave EXIF intact.