I own an SD14, and had previously owned an SD9. What the Sigmas do well they do very, very well indeed. And what they don't do well - well, they don't.
There is a clarity and sharpness that X3F (the RAW format) images have. There is no AA filter, so there is no blurring. Details can be sharp and crisp without mush or smoothing. Only in rare and exceptional circumstances is moire to be found, and then there's no multi-colored rainbow effect. Colors are accurate and vibrant, and not exaggerated or over saturated. Because of the stacked pixels in the sensor, B/W conversions can be stunning. The SD10 has a removable IR filter, and it's even easier with the SD14 to get IR imagery. Layout of controls is simple, without a lot of different "modes" such as landscape or portrait - It is very much like a film SLR.
On the other hand, they produce huge files, and the Sigma's don't have a huge buffer. Additionally, images can get to be pretty noisy when underexposed and pushed, or at high ISO - especially in the blue channel. Autofocus can hunt, especially in low light.
When combined with good glass, you can get eyepoppers. Probably not the easiest for low-light action or weddings, but there are a number of really talented photographers who use them in weddings and get great results.
If you search pbase for sample SDxx images, you will see that most are of the landscape/macro variety.
Depending on the RAW converter, yellows and blues can get electric, but using Sigma's PhotoPro 2.3 or Iridient's Raw Developer does a better job than ACR; Raw Developer is fast, too. Yvind Strom did a tutorial on learning about the camera. It can be accessed here:
http://www.foto.nordjylland.biz/SD14/SD-usertips.htmAs far as why are the Foveon sensors not universally used, a lot also has to do with the uniqueness of the fabrication process, as well as the processing power in camera needed.
Hope this helps.
tjh