There even are some dentists in Germany that drawing teeth without anesthesia, but with the help of hypnosis.
And the pain (for drilling+filling a regular cavity) really isn't that bad. The biggest unpleasantness about it is that it's in a weird place, at the back of my tooth, and that it happens at pretty much random intervals beyond my control. Most of the time you don't feel a thing, until the dentist "hits a nerve", sharp pain, but also subsides within the second.
And as far as I've understood they don't actually hit the nerve with the drill, just that the tooth locally heats up from the drill friction, which the nerve feels as a sharp pain, anyone know if that is actually right? I just ... read it somewhere :)
I used to do without (it wasn't offered, I was young and didn't ask either), but nowadays I usually opt for the anaesthetic, I don't mind needles at all, and there's no downside to it, afaik.