You can do the experiment yourself. Shoot a number of shots hand held, then do them again on a tripod. I did this a couple of years ago and discovered that I can clearly tell the difference - at high magnification - between hand held and tripod even at 1/1000th with a 50mm lens. The longer the lens and the longer the exposure the wider the gap gets. (*) And I would not categorize myself as a complete ninny when it comes to hand holding. (In fact, I do shoot hand held plenty, and I would say that most people would approve of similar results.) But it's still better with a tripod.
It's worth mentioning that the experiment above was done with best shot discipline, for each category. Ie tripod was locked down, VR off, remote release, mirror up, etc; hand held was with elbows braced, exhale, VR on if shutter speed was appropriate but VR off otherwise, etc.
For me the answer is to use a tripod unless there's a good reason not to. And those reasons do exist, often commonly. One doesn't shoot sports on a tripod very often, and certainly not things like basketball or skateboarding. Tripods are often prohibited, for one reason or another. Etc. But there are plenty of times when tripods are a better idea.
And here's another thought that may be new: even though a fast lens and/or higher ISO may make it possible to avoid a tripod - at least from a shutter speed or ISO perspective - I find that leaving the tripod behind is still a bad idea, for at least two reasons. First, light levels don't really have that much correlation with DOF requirements. If it's really low, perhaps you can't really see too far into the background, but otherwise it's a thematic or compositional choice. In general I find that DOF is close to independent of light level. Just because the light is low doesn't mean that the subject and/or composition are ideally suited to f/1.4 DOF. Secondly, even if you can manage to squeeze off shots at ISO 6400 and f/1.4, I find that there's really no flexibility in that. In a low-light situation I'm really a slave to the shutter speed and ISO combination. (And note that I'm willing to go a lot higher on ISO than many, because I have an FX body, because I am usually willing to tolerate more noise in the final result, and because I'm definitely willing and able to apply post processing to reduce the impact of noise.) In a night (or dark, such as an art museum) shoot I can describe a distinct feeling of relief when I bring out the tripod, because then I know I don't have to worry much about shutter speed and ISO. I can select them according to the subject and final output requirements, rather than being forced into specific combinations by the light levels. I find that I'm far more willing and able to exploit the whole range of options available on the lens, shutter speed and ISO when I have a tripod at my disposal. In this regard the tripod certainly provides me with the ability to get higher quality results, not really for sharpness reasons, but because the composition is not as constrained by technical limitations. The results are qualitatively better, not merely quantitatively better.
And even in the quantitative category... even if you have hands of stone and can match a tripod for steadiness, remember that basically no lenses are as sharp wide open as they are stopped down. Even if you have a don't-care situation for DOF (say you're shooting an entirely flat subject and you are able to place the lens axis perpendicular to the subject, thus minimizing DOF requirements), you will likely get a sharper image at f/5.6 or f/8 than at f/1.4.
And we won't even get into situations such as bracketing, focus stacking, pano stitching and the like...
As an example, last winter I was in Munich and I ended up with a shot at one of the churches. I didn't have a tripod. The image looks pretty good, actually - 1/30th, f/4, ISO 25600. The lens had an f/4 maximum, and at 8mm, 1/30th it was not particularly in danger of the 1/focal rule of thumb. But ISO 25600? Even with aggressive and detailed noise reduction (I did the noise reduction with Topaz Denoise on at least four separate layers), it simply can't be as good as if I'd have shot it with a tripod. With a tripod I would have shot the same composition at f/8, ISO 200, and 9 stops slower shutter speed (30 seconds?). Which do you think would have been a better technical result? Now consider that this was a tourist area with lots of people milling about. The 30 second exposure would have rendered most of them as either a faint ghost or completely invisible. If I had faint ghosts, I could have shot a second frame from exactly the same spot, and used the HDR function in Photoshop to eliminate the ghosts. So this one would have literally had different contents in the final result, in addition to being able to use a noise-free base ISO, a better aperture, and slightly more appropriate DOF.
(*) I'll add that I did the experiment on a 12mp sensor, with what would generally be described as run-of-the-mill Nikon lenses. Ie no duds like the 43-86 pre-AI, but also not legends like the 200/f2 AFS VR or 60/f2.8 AFS. Those were done with the 50/f1.8 AFD, 35-70/f2.8 AFD, and Tamron 90/f2.8 - all excellent lenses but probably also not the equal of the best available. I don't have a D800e or a Hasselblad, but I assume that the margin is even wider with a super-high-res sensor and/or top lenses such as (say) the Zeiss 100/f2 Makro, Coastal Optics 60, Nikon 200/f2 AFS VR, or even 14-24/f2.8.
_____ Brian... a bicoastal Nikonian and Team Member
My gallery is online. Comments and critique welcomed any time!