The millitary adopts pistols in a joint-service fashion. So all the services technically have the same standard pistol. The two standrad pistols are the M9 (Beretta 92) and the M11 (Sig P228). Which one you get depends on what your unit's mission is and what you're authorized. Despite what Hollywood stories make it out to be, you get what you're issued and there's not alot of choice. I've seen CID, NIS, and some other folks with the M11. Most of the other folks that are regular issue get the M9.
Some units are allowed other choices based on their needs. These may not be standard issue items (like the 686, HKP9S, P226(Mk 22), MEUSOC 1911, etc) that are purchased by the govt, or the units themselves with specific funds for this type. Some of them (like the MEUSOC .45) are merely adaptations of existing service pistols that cost very little to modify compared to purchasing a new handgun of the same capability.
The MK23 HK was designed for a specific requirement by SOCOM, and funded through that command, so you aren't going to see Mk23's in the hands of regular GI Joe. Only a unit that needs that capability will have one. They are huge by the way, and not the perfect back-up to a long gun.
Obviously, special units get to pick from a variety of wepons that are on-hand. That could be anything captured or tested in the past, which is a pretty long list.
Some units are authorized to allow personal weapons, but these are rare and far fewer than most people would believe. Your regular GI gets issued a pistol from the arms room, and that's that. The two standard pistols are the M9 for general service and the M11 for units that need soemthing smaller for some reason.
As to what gets picked, it will all depend. Pistols are not all that important in the bigger scheme of things, and many SF units won't even carry them on some missions. It's just dead weight. ON some missions, they may be the difference between success and failure.
When and what gets used is based on the mission requirements, and that's why they are trained to know when and what to use. There was a problem with the SEALS shooting SMG ammo out of the M9 and breaking slides, so they went to the MK22 (P226). There was an old joke that the Marine Corps came up with a better idea and issued .45's so they couldn't shoot SMG ammo (9mm) out of it in the first place. You'll note that the Marine Corps factfile states the MEUSOC .45 is issued to Marines equipped with the MP-5 9mm. You'd have to admit it sure would eliminate the problem of shooting 9mm SMG ammo out of a pistol, if you made the pistol a .45.
Ross