In the early days of SFOD-Delta, the guys tended to use 1911s for most of their CQB work. Now, there are different situations to consider and a different tool for each situation, but these guys had a few important reasons for this choice. First, it was/is very easy to handle/maneuver in tight spots (read, airplane cabin etc). Second, the round was much less likely to go through a target and hit a civilian/hostage/penetrate the cabin of a plane, but still had sufficient energy etc. at close range to cause damage, even to a target wearing some type of body armor, although this was not a primary concern back then because body armor was not as common in the world as it is today. Third, these guys shot ALOT...they were (and are) some of the most, if not the most proficient guys in this type of combat in the world. They were good enough that they could use pretty much any weapon in CQB with an excellent result.
So, my conclusion would be that it totally depends on the scenario, especially if hostages/civilians are present. In that case, it is more likely that a round that will not be likely to overpenetrate or go though a target is a much more likely choice, in a weapon that will be easy to maneuver and quick to get off follow up shots, because the likelihood of a target being shot only once in this situation is very slim because they need to go down, and fast. If hostages aren't present, or there is a significant possibility that the targets will be wearing armor, or if there is a likelihood of longer-range engagement during the mission, a carbine 5.56mm is probably a more viable option.
In a real world-type scenario for today, it is probably likely that if we were to see the kit for a SEAL team tasked to a mission in Afghanistan versus the kit for a SEAL team tasked to retake a cruise ship like the Achille Lauro back in the 80's, the following differences would likely be present: the guys in Aghanistan would likely have weapons that would be viewed more traditionally as Army weapons like M4's (or possibly some variant like the HK-416 that SFOD-D has been using) firing 5.56, with a squad machine gunner with an M249 and a DMR-type guy with a longer-range weapon like a modified M14/M1A. The guys on the cruise ship would likely have a more MP5-ish weapon like a UMP or possibly a 9mm MP5, both with all the bells and whistles you can imagine. One or more of them may also have a small 12ga shotgun, which would likely be more for breaching-type purposes than CQB, but might get used should the situation dictate its use. They would probably also have a sniper-type guy or two set up at strategic locations to provide cover or intervene in not-so-likely situations. Weapons like the PS90 or some very short 5.56 variants might find themselves into this situation, especially is body armor could be present or if the user just feels more comfortable with that weapon, but I don't know much about that type of choice. People always say SOCOM guys get whatever they want, and to some extent that is probably true, but it is probably true to a much greater extent that they get what their commanders want them to have.
Just some thoughts...