This texture pack makes water more transparent, it also removes the water overlay. If used with fullbright and the no fog mod, you can achieve the best results. Water still looks like water in this texture pack, but makes you able to see better.
Disclaimers
Make sure you ask the server owner if this texture pack is allowed, as some servers will not allow this texture pack.
All this texture pack does is change the water overlay and underwater texture to transparent, brightness and fog are the main things determining underwater visibility. This texture pack when used with those will perform at its best.