Depends on what you want to spend, your case size, etc. A good air cooler can beat out all the cheap water coolers.
Water cooler. Looks cool, can really cool a CPU. Doesn't cool the MB anymore, so you get more heat there. with a big block of copper and fans on the CPU, it by proxy, helps cool the motherboard and RAM a bit too. A water cooler just has a small block to replace all that, and thus, you need good air flow in your case to compensate for that.