Yes, if you also plan on cooling your GPU(s) or some other part like your NB or Memory. Also if you really like overclocking it could be quite useful. I don't use it primarily do to the price and a water cooled comp being hard to transport constantly.
If you are only going to cool a CPU and it wont be OC'ed to an insane speed then a good HSF will be fine. I have a Q6600(G0) at 3.5(1.5V) and it doesn't overheat. If I was looking for 3.6+ then i would need water or something more crazy to keep it cool.
It will remove more heat from the entire system allowing all of your componets to operate cooler and potentialy OC further.
A good water cooler will allow you to keep your componets far cooler than any air cooler.
My first (and current) water setup uses this Swiftech kit.
It comes with everything you need to get it running, sans a dremmel to cut the holes for the tubes.
I would highly recommend it as a great starter kit that has room to upgrade with.