Actually there is no CPU or GPU out there which really needs to be water-cooled. A graphics card which comes with an air-cooler has to be designed in order not to burn even under full load. Else it is just broken by design or defective.
However there are other reasons to use a water cooler. For example my personal workstation is entirely water cooled (including CPU, chipset, GPU, PhysX card and HDD drives. But my reason to do water cooling is just the noise. My water cooling system is entirely silent and passively cooled. Well, I am still operating some low-spped 140mm fans at around 300-600rpm within the case to cool the voltage regulators and pieces which are not directly water-cooled. This is usually required since if there is no air moved at all in a water-cooled system the mainboard and mainly the voltage regulators could burn quite quickly.
I also have to say that my 8800GTS-512 was not nearly as silent as modern graphic cards. Modern graphic cards usually reduce the clock rates and voltage dramatically in idle mode and therefore there is much less heat generated by them when not under load. So usually the fans are silent in normal operation.
Under very heavy load situations most cards start to spin up fans then. However this is usually during gaming operation where it does not matter much as you're listening to sound anyway. CAD programs and other tools making some use of GPU are usually not loading the card that heavily; so they still keep an acceptable noise level.
Also keep in mind that replacing the air cooler with a water cooler voids warranty - so only do it if you're really sure you need it. Or just buy a card with pre-mounted water-cooling block. However these cards are usually hell-expensive.
So to answer your question - there will never be a need to water-cool your graphics card if you operate it at stock specs. If it overheads during normal operation it's defective by design.
Of course the story also changes if you plan to overclock the card - but this voids warranty anyway.
So for me there are two valid reasons for water cooling:
- Reduce noise level
- Heavy overclocking experiments