Yeah that is nice. Check out real flow in 3ds max.
What that requires is thousands or millions of particles.
having that in a game in real time would defeat the purpose of optimization(making a game run at its best).
What they do now is make the water a collision object and when the player or asset collides, the water spawns more particles.
They use that to simulate water. If you are a college student you can play around with 3ds max for free at autodesk.com
Blender, a free software also has a very nice water simulator as well. Andrew Price has also done a very nice tutorial to show you how it works.
http://www.blenderguru.com/videos/creat ... imulation/
I am a college student with an associates degree in computer animation and pursuing game development as well.