The water I am using is usual in so much that I am using Sculptured Prims (sculptie). Sculpties are very different to normal prims and cannot really be made in-world. You have to use a 3D modelling tool such as Blender which is free - http://www.blender.org/ and import the image as a texture into Second Life. I won't pretend to understand the maths behind it all but essentially colours (Red Green Blue) are used to represent X, Y and Z space. If you would like to read up on sculpties, look here... http://wiki.secondlife.com/wiki/Sculpted_Prims:_FAQ#SculptTextures
What sculpties give you is a much more realistic feel about organic shapes. Objects with curves, in all the right places, (as Mika would sing - if you've got 3 mins spare - http://www.youtube.com/watch?v=tcRiXOONqf0 ) are now embedded into this virtual world. So plants, animals and yes, water become realistic shapes rather than bits of shapes stuck together to give the impression of a whole. The difficulty now, is to use the sculpties in such a way to capture the realism offered. Lots of studying of real waterfalls to get this right, I think.