What it stops you doing
Building over or near it, generally. Water companies control development over their apparatus, and work affecting it needs their agreement — sometimes a build-over agreement, sometimes a diversion at your cost.
The practical effect lands on extensions, outbuildings, garages, swimming pools and anything with foundations. If the plot's only sensible extension route crosses the main, that is a material constraint on what you are buying.
A diversion is usually possible and is usually expensive. Get an indication of cost before you commit if your plans depend on it.
The access right that stays for ever
The water authority keeps statutory rights of access for maintenance and repair. If the main needs work, they are entitled to come and do it, and that can mean a trench across a lawn, a drive or a patio.
Reinstatement obligations exist and “reinstated” does not always mean “as you would have done it”. This is a reason not to put anything irreplaceable over the line of the pipe.
Before you exchange, ask for the plans, have the line established on the ground, and check whether any existing structure already sits over it — because if one does, somebody built it either with an agreement you should see or without one you will inherit.