Function
Adding limestone to water changes its chemical structure. Because the water we drink is not just H2O but also contains minerals and ions, it is important to balance them. Limestone acts as an alkaline to help balance the pH so that the water is less acidic.
Effects
More balanced pH can diminsh corrosion in plumbing and sink faucets.
Considerations
You can't expect to pour limestone down your well and treat the water as ground water flows similar to a river. To add limestone or another alkaline, you will need a water treatment system.