I have been shooting Sigma DSLR's for a number of years, since the SD-9. I got into the system when I was moving out of film SLRs into digital and did a lot of research before I made the leap. I too came across the the Foveon chip and the design of it struck me as much more sound than the Bayer design on a conceptual level; plus I really liked the images I saw coming from the camera.
The way to think about the difference here is that a traditional Bayer sensor it really taking three separate photos - one green, one red, one blue. For a 14MP Bayer sensor the green photo is has 7 million pixels, while the red and blue images have 3.5 million pixels of data. None of that data spatially overlaps; that is to say if an object were just one pixel high as captured by the sensor it could vanish in any one of the images depending on color. At any given spatial location 2/3 of the color data is discarded. So while the output you get from a 14MP camera might have 14 million pixels in it, it's essentially a re-sampled and upsized version of the image with greatest detail - the 7 MP green image.
On the foveon side, there is no-where a color in the image can "hide" because at any given sensing location, the full spectrum of light is captured by the three layers of sensors and so there is not as great a need from input from neighbors to resolve what the sensor saw.
The end effect is that Foveon sensors will not be fooled into thinking fine detail is really some kind of color (color moire), and the level of detail captured is constant because no fine detail is accidentally discarded. The bayer sensor discarding 2/3 of the light at any point can sometimes drop fine detail that the Foveon chip will resolve - again it depends on scene color.
Because the level of detail in a Bayer sensor is variable, it can be very hard to compare with the Foveon chip as far as detail captured - but a rough rule of thumb is that a Foveon image will capture around the same level of detail as a Bayer camera with 2/3 of the Foveon MP rating (or sensor count). So for example the upcoming SD1 has 46 million photosites (sensors) which means you can expect similar levels of detail to a 30MP bayer image. But this is again an image without color moire, without an AA filter in front of the filter (when you don't worry about color moire you don't need an AA filter).
You can see some interesting examples comparing the original Canon 5D to the Sigma SD-14 here:
http://www.ddisoftware.com/sd14-5d/
Especially note what happens shooting color targets to get a sense of how detail can vary.
So all the technical stuff aside, what does the sensor do well with? Because it's capturing the full spectrum at every pixel and the same level of resolution regardless of color, I think it captures subtle tonal changes really well. That means really nice skies, or anything else with gradual changes in color or tone. As such they produce really nice images for B&W conversion also, because of the very smooth transitions between tones.
http://www.pbase.com/kgelner/image/90304998
http://www.flickr.com/photos/kigiphoto/5308324073/in/set-72157625711613108/
http://www.pbase.com/kgelner/image/108588990
(full size versions of each of those images can be found at the links).
Where the sensor has had issues, is with higher ISO - the current cameras can do ISO 3200 when asked:
http://www.flickr.com/photos/kigiphoto/4684772878/in/set-72157624236424558/
but really 800 is more of a realistic limit to most shooting (unless you are shooting for B&W and then those images can hold up really well because of the nature of the noise).
The Sigma cameras are not really oriented to people starting out with photography, because they don't offer a lot of assist modes or things of that nature... so be aware of that if you are thinking of getting into the system. The easiest way to get into trying out the sensor for yourself is the Sigma DP-1 or DP-2, earlier versions of the cameras can be slower to use but all of them will give you a good taste for the detail and color the images capture.
Note that I am obviously not an unbiased source, since I have enjoyed using the cameras for a long time. So the other thing to do even before getting a camera is to go exploring images from the sensor in more detail. I provides some above and you can explore my sites as I generally only shoot Sigma cameras, but you can find a ton of example images from all of the various cameras Sigma has produced here (also with full size images to be found):
http://www.pbase.com/sigmadslr
Also you can find a ton of great info at Carl Rytterfalk's blog:
http://www.rytterfalk.com/
Somewhere in there he has sample RAW packs you can download, and various things talking about Sigma cameras, lenses, and the Foveon sensor. He's a great photographer and very enthusiastic as you'll see if you watch any of his videos.
EDIT: Carl has just written a lengthy post of "Why I use Sigma", which directly applies to this question:
http://www.rytterfalk.com/2011/01/20/why-i-choose-sigma/
The summary of his reasons are:
- Nuances (in color)
- Density
- Micro contrast
- True sharpness
- Dynamic Range
Which he goes into in more detail at the link, along with some more images.
One side note I forgot to mention, that is not really directly about the sensor, but is about the Sigma specific DSLR's that house the Foveon chip - you can easily use them for IR work as well just by removing the dust protector on the camera (built to be user removable and reinstalls without any tools).