Any handgun in Rainbow 6 Vegas 2; infinite ammunition and usually instant kill if headshot; considering most can have a magazine of 15+ rounds, there is no reason why not to use it; even the Desert Eagle and Raging Bull are awesome, mainly because they are so powerful they can easily get collateral kills, even if they are a bit slow. I am not surprised that some higher level elites only use pistols. I even found the piddly-power Glock 18 to be useful in some situations, especially if you don't otherwise have an SMG.
MP443 Grach in BFBC2; dang that thing is fast and accurate and killy.
All of the revolvers in the base game in Fallout: New Vegas; all are at least decent power and accuracy and I like the sounds they make. Even late game I still find myself using the .357 once in a while (specifically the unique one).
Alpine, the unique SIG Sauer P220 and March, the unique USP Compact in Stalker: Call of Pripyat; both of them were just brilliant; they had a really good balance of power, accuracy, magazine capacity and could be silenced. Oh and plus when fully upgraded they could be fired in full-auto.
The M1911 in CoD 4 was awesome; shame it wasn't in MW2, but then again I disliked that game so probably for the better; the BlOps version isn't for me either; I don't like the finish and the iron sights don't work for me, even when upgraded. In CoD 4, it sounded awesome, it looked awesome and it handled awesome, so I found myself using it a lot, despite the inferior magazine capacity.
The CZ75 in BlOps was brilliant; I particularly liked it with upgraded iron sights, but extended magazines and dual wield were pretty good too; wasn't so keen on the full-auto because it just seemed unnecessary as I could fire it that fast anyway, but more accurately. Its bigger magazine capacity than the other pistols was useful, although that said, the ASP actually was quite good, especially because of the sights and the fact that reload is as near to instantaneous as I've ever seen without an infinite ammo cheat.
As you can see, I'm actually pretty big on handguns in games.