The SNES is basically limited to 256x224. It has a higher res mode for BG (not sprites - they are pixel doubled if you will) but it's not practical and you lose a lot of the SNES abilities with it. I know of one game that used it for text box for an RPG.
Saying stuff like 256 or 320 horizontal pixels is misleading be cause it's not the same as with PC res of the VGA tweakable register set. You sometimes see mid res referred to as 320, 336, 344, or 352. It can be any of those and still have the same pixel size. To keep things simple and the ratios the same, the res' are 256, 344, 512.
Using the 256 res gives some advantages. First off, in relation to a picture onscreen it uses the least amount of video memory. This means you have the most room for tiles and sprites in VRAM. Second, the pixel aspect ratio is easiest to work with. Its pixels are more "square" than the other resolutions in appearance. Third, it offers the best configuration for sprites on screen before you run into sprites per scanline limit. If the limit is reached on a given scanline, the video processor will stop fetching/showing sprites for the rest of that line. The solution is alternate sprite orders,i.e. flicker. With flicker you can at least see the sprites if overflow/limit happens. The sprite limit per scanline is 16 sprites or 256 pixels - which ever comes first. Sprite limit is effected by the whole sprite, not just the visible part. If you have a sprite "bullet" that is 4x4 pixels, the sprite itself is really 16x16 pixels. So if a visible sprite is 18x16 pixels, it uses a 32x16 pixel sprite. Makes you wonder why some PCE games use small objects when they have such extra space for free.
Some programmers are really good at sprite management and other were careless. One example is Parodius. That pirate cat ship in the first level with those cat paws as oars? Those cat paws are extra sprites on top of the cat ship made of sprites. Same with the cat head. They could have easily made a merged layer for the cat paw animation and the cat head, reducing the sprites by quite a bit and this reducing flicker - but they didn't. The same goes for SF2:CE. There are quite a bit of places where they wasted sprite layers, causing flicker (done a lot of hacking with that game).
The problem with the 344 and 512 res modes is that you still have the same 16 sprites or 256 pixel limit. It doesn't scale with the resolution as it should (there's enough video processor resource, its a hardcoded limit). Like with Chris said, it requires a bit more optimization and cleverness to use the higher res modes. So, other than the sprite limitation it comes down to memory. For hucards that means a larger rom and for CD games that means CD RAM is less efficient.
Quite a few Genesis games use the 256x224 res mode to save on memory. Street Fighter 2 is one them. And SNES games are pretty much stuck with it.