If you're just going to render stuff out to sprites, you can animate and use a Sculptris model in blender for that purpose. I wouldn't, I'd retopologize it for better animation, but that's me. You could study blender - which I mention because it's free - but Sculptris is a fine companion to any other 3D workflow.
Don't be so quick to judge. I think I get what he's talking about.
For one, check out some direct x 11 videos. This is the initial route dynamic tessellation is taking in games. They are basically going to take all the bump maps and normal maps from games and use them as displacement maps as the approach the camera. So you will get a lot more detail on things like cobblestone roads, walls and fences and thatched roofs will actually look like leaves rather than flat planes.
But, queue some forward thinking and an increase in poly counts for characters. Throw in physics and its possible that dynamic tessellation can be used to dynamically deform characters and landscapes. Imagine being able to actually put a bullet hole in your target or take a chunk out of their shoulder with a shot gun. Also, for fantasy fans, you can do real sword slashes into the enemy or create a localized dent their armor with a mace. For the Age of Conan fans out there, crushing a skull fatality comes to mind...
I will remind you that sculptris does a good job of not ratcheting up the poly counts, the poly counts do go up and over time, can balloon into machine crawling levels. So I don't know if it will scale to the point of Fallout-style fatalities. There is a big difference between an acceptable frame rate for sculpting and deforming a character based on a weapon "brush" that fires tens of bullets per second.
So I was wondering, is it possible to use Scultris to achieve the same thing in a 3D game?
Speaking as someone that's done game related stuff ranging from 2d sprites to 3d character models
you basically have 2 realistic options, create normal maps and use animated textures to reveal an underlying mesh, flesh bones ect, which is how they are doing most of the current 3d stuff typically you will need 3 models for this, one for flesh (also used for regular animations. Another for the muscles, and finally one for the bones. Blood and other fluids can be achieved with particles or textured planes.
the other option if your going sprite based is to model each frame of the animation in sculptris and capture it from your 4-16 angles
the first option will take you much less time if you're planning on a decent frame rate, the video you linked shows a very low frame rate that may be the result of the video or system