There are two different kinds of water spots: (a) mineral deposits in the water that form spots if you let the car sit wet; these should just wash off, and you can avoid them forming by towel-drying or blow-drying your car when you wash it; and (b) outlines of water beads that are actually etched into your finish; these form when your car sits in the sun after a rainfall, and to remove them you will need to use a product that's at least slightly abrasive (such as Meguiar's or 3M Swirl Mark Remover or a polishing compound).
Which kind do you have?