When is a steep takeoff appropriate?
The short answer is, when the surrounding environment requires it. The procedure here is perhaps most akin to a short field take off over a 50 ft obstacle but a few things are going on. The aircraft, as soon as it leaves the ground is climbing at Vx which allows for the maximum angle of climb.


  Vx is the speed for best angle of climb while Vy is the speed for best
  rate of climb.


In your linked video its mostly for show. In practice short field over a 50ft obstacle takeoffs are used for, well, short fields... or when you need to get off the ground fast so as to get over some obstacle at the end of the runway. Generally speaking most commercial airports have long runways that are more or less obstacle free (with some notable exceptions) so short field maneuvers are not what they are in general aviation. There are lots of small fields all over the country that have trees (also known as 50 foot obstacles) power lines, buildings, church steeples or any manner of obstacle at the end of the runway. On top of that many of these strips are on the shorter side (under 3000 ft.) so you simply have less runway to work with and your margins can be far smaller. In this case you want to get off the ground as soon as possibly and climb as fast as possible. 

It is the pilots responsibility to operate the aircraft safely and these kinds of maneuvers are well within operating limitations. In small planes they happen more often than you would think, especially if operating out of a short strip. and even us lowly private pilots are trained in short field 50ft obstacle clearance maneuvers. As for the big guys if the airport environment calls for it, it may be performed but I would think it is less common as most commercial airliners can pitch up far more than people in the back would be happy with. Someone with more commercial experience may be able to elaborate on how often they happen in practice there.   

There are a few things to keep in mind as well, military planes simply have a much higher thrust to weight ratio than commercial airliners and can in some cases sustain vertical climb outs. Military jets also often have afterburners for an added boost. The commercial planes you see in the linked video are more than likely lightly loaded carrying only a partial fuel load. Camera angles can also make it look as though the plane is climbing much steeper than it really is.