When I was little, it was terrifying from the sensation of being out of control. Also used to get this butterflies in the stomach sensation when I descended rapidly, though airplanes, elevators, and ferris wheels could trigger that.
It took some time to outgrow those, but while there's still that "what did I get into?" moment when looking down from the top of the Millenium Force at Cedar Point, it's such an adrenaline rush plunging down the hill.