Ah, the pistol.
The dependable sidekick that swoops in when the hero (main weapon) can't get the job done.
Unless, you know, it's one of those games where the pistol is depicted as a peashooter that can barely put a scratch on the weaker enemies.
In Bad Company 2, many a foe fell to my Colt M1911. Despite having a pathetic mag size, the shots got the job done. Took about two shots to kill, so it was an entirely feasible self-defense tool that could take down several assault rifle toting enemies with a single magazine (three, with one more fairly maimed).
In Team Fortress 2 it's the master mid-range heckler for the Engineer and the Scout, and puts the pain down in close range (to the point where it's a completely viable alternative, if not a slight upgrade, to the shotgun in terms of DPS) too.
In games with stealth as a focus, or a great way to go about, silenced pistols are just lovely.
Be it Hitman, Human Revolution or Metal Gear Solid. I think I used it as my main weapon for the first two listed there during my first playthroughs.
In survival horror games, it's the early game means of defense (next to that lead pipe) and the thing that you can rely on in terms of ammo better than the likes of shotguns and rifles (meant for the more powerful enemies, if at all).
I won't even get started on revolvers (aka the master race of sidearms).