Fountains are designed to please the eye, but they often produce disappointing images when shot during the day. However, many are dramatically lit at night, and are better isolated from their background in the dark.
There are basically two types of fountain shots. One type has a pleasing soft blur from the water motion, which require a long exposure, and thus a tripod; this type of shot is excellent for including both the fountain, and a lot of detail about other elements in the image, such as architecture in the background. The other type of image is ones that have sharp water detail. Digital cameras have improved to the point that a tripod not actually necessary for the freeze-motion fountain shots; after all, to freeze the water, the shutter speed much be quite fast, making hand-held shooting possible, though multiple shots are a good idea, so that the steadiest shot can be selected.
The shot below is a handheld image of a fountain in Zurich, with lots of very satisfying frozen water detail. The image was actually darkened a bit from the default RAW settings, to reduce the emphasis on the background, and noise in the darks. With the latest DSLRs, there would be even less dark noise, and other processing options opening up the background further would be possible.
C. David Tobie