My opinion is that water cooling is a waste of money if you are not overclocking.
Regarding water coolling, there are two types, water cooling kits that usually contain an enclosed system, and custom water cooling.
Custom water cooling is usually for the extremist who will be pushing overclocks. Building a custom water cooling loop is expensive, time consuming, requires planning, and requires regular maintenance (usually monthly). Expect to pay 200+ USD.
The kits, such as the ZALMAN CNPS20LQ Ultimate Liquid CPU Cooler
are contained and designed to be maintenance free. However, it should still be notes that a liquid is in your computer and a leak can cause problems, so inspection on a regular basis is necessary.
The water cooling kits are usually used for a portable computer, as the very large copper heatsinks can wobble a bit in transport, and also used for moderately high end cooling on more expensive kits such as the CORSAIR H100 (CWCH100) Extreme Performance Liquid CPU Cooler
These water cooling kits in general produce high performance (although not comparable to a custom set-up) while keeping a small profile and low weight.
However, most water cooling kits are beat by similarly priced air coolers in terms of cooling. Most water cooling kits are relatively quiet, whereas a typical air cooler may be louder. Expect to pay 50-150 for a water cooling kit.
One potential problem with water cooling kits would be if you want a dual radiator, you would need a compatible case.