Water is a more efficient conductor of heat than air, and when properly set up it therefore has the potential to move more heat out of your computer, faster. Be advised there are a bunch of cheap 'solutions' out there. And as you would expect, enthusiast advise is generally along the lines of
"Go Big or Go Home", though in this case the reviews of pre-made liquid cooling products seem to back this up. I haven't seen any reviews where premade stuff beat good air cooling.
Downsides?
Expensive, more of a pain in the behind to set up, has to be maintained with regular flushes and Algicide so things don't grow in your coolant, and a failure will leak on your expensive components. Leaks are obviously very bad mojo for computer parts.
I do overclock, but don't liquid cool. If you are careful in your parts choice and diligent about maintenance, you should have no troubles. I know that I'm OK on the first part, but greatly less so on the second. And since I'm not pushing all that hard, I don't see a whole lot of point to it. Though the recent addition of a somewhat noisy 4870X2 GPU has me thinking about it again. Not really because I'm worried about temps, but rather for the potential to quiet the computer down.
My best advise is to do a LOT of research before you make the jump,
if you decide to make the jump at all.
Good resources:
Danger*Den <<== Seems to be the apparent leader
FrozenCPU.com | 1.877.243.8266 | World's Largest Selection of PC Modification Supplies Quiet PC USA - Quiet Computer Hardware - Soundproofing Acoustic Material Kits