I have no personal experience so everything I state is from sheer lack of expertise. Benefit from that!
I have once encountered someone who used a SSD as a boot disk and while read times were very good, write times were severely lacking after a few months of use. Read around 180 MB/s and write dropped to 35 MB/s. It may have been the brand/model he used, I just don't know.
What kept me away from them at the current time is the price. I currently have over 16 TB of internal storage, Can you imagine the cost of having that capacity in SSD format? Mind boggling! When I add to that the limitations of the PCI-e bus and raid controllers, there is no sense in investing in SSD's for mass storage. For a boot disk in raid0 configuration with two SSD drives, yes it makes sense if money is no object. The question is, how many people consider money no object nowadays?
It'll be cool in 2-3 years when they go down in price and get better.
If you use the SSD as your boot drive/program drive you'll notice speedier OS startups and some programs open up faster as well. Depending on the drive, Photoshop can open in about 2-4 seconds, for example.
There are massive differences in quality and performance though. Currently the "best" in the solid state game is the Intel X25-E, then the Intel X25M.
Here's a review about it http://www.tomshardware.com/reviews/intel-x25-e-ssd,2158.html
I've seen tests of other drives though that actually performed worse than some nicer "regular" disks, like WD (Veloci)Raptors, Caviar Black etc.
I'm sure there will be even better drives out by the time you read this LOL
Either way this stuff's still really expensive, particularly because you'll want drives larger than those 32Gigs if you plan on running Vista/XP/W7 and Adobe Creative Suite plus other stuff on it.
SSDs would also be good for your pagefile, possibly even preview files. At this point I wouldn't use them for media (push or pull) simply because they're too expensive for what they deliver, capacity isn't what most pros need, and there are just too many differences in quality and performance between manufacturers and models.