Half Life 2 had and I think always will have the best hand gun, that big ol' revolver, I remember the first time I played it, shortly after getting my 5.1 Logitech E5300, the thud that was emitted from that subwoofer when you pulled the trigger, biblical. It was just perfect, and the damage it dealt, it could send those pesky guards flying across the room with a single bullet.
Destiny had some fun guns, but Destiny itself was never terribly fun unfortunately. Boderlands just had too much for any single handgun to be as note worthy. And most other games just make pistols out to be side arms that have no range, or lose too much power over anything more than short range to be of any use. With the exception to the Desert Eagle witch fortunately does get portrayed as the hand cannon it is most of the time and can be fun to use, Deagle rounds in CSS were among my favorites, also I had it skinned to be the revolver from Half Life 2, so lots of that awesome thuddy bass every time you pull the trigger.