You know those massive, intricate, and awesome looking combos that you usually see in combo videos & online matches? They're a relative rarity in tournaments, where short, cheap bread and butter combos are immensely preferred.
In many Bleach fighting games, Soi Fon has an attack in which she can use her Suzumebachi to, as in the anime, get a two hit KO - if she hits an opponent with the attack twice in one round, they instantly die. The impractical part comes from the fact that the attack is usually slow and hard to hit with, uses up a ton of spirit pressure, or both; and that the first hit usually does no damage. At best, using this attack can serve as a sort of alternate fighting style, as using it can be an all-or-nothing strategy.
In Vampire Savior, the mummy, Anakaris, has a flashy super where he casts a slew of magic spells on the opponent and then crushes him with a gigantic sarcophagus. Definitely his flashiest super, but it's also one of the hardest to execute, and requires you to hit your opponents with an extremely slow, heavily telegraphed, blockable projectile.
In God Hand, the main character has a variety of "guard breakers", attacks that stun a blocking opponent. These range from sobats to flying kicks to haymakers to spinning backfists, but the best guard breaker throughout the entire game... is the basic, boring overhead chop. Plus it somewhat adheres to Real Life in that misuse of the powerful-but-slow moves usually gets you punished.
The biggest offender as far as God Hand is concerned is definitely Yes Man Kablaam, a ridiculously slow and uneffective punch that leaves Gene completely defenseless as he turns his head and smiles to the invisible crowd after the attack. Its only major advantage is that if the punch connects, it raises your Tension bar by a lot, but it's generally not worth it...unless you learn to dodge-cancel out of the recovery.
Instant Kills in the Guilty Gear games do exactly what the name suggests: finish the round in favor of whoever connects one. Most of them look pretty cool, too. Unfortunately, to keep them from being game breakers, they can only be used once, they're extraordinarily difficult to hit with unless your opponent isn't paying attention (you have to switch into a "sudden death" stance, complete with a glowing outline, making these better suited for Mind Games and last-chance desperation attacks), and if you attempt one and miss, you can't use any move that requires meter for the remainder of the round. And because this is Guilty Gear, which places a lot of strategy on meter management and aggression, chances that the Instant Kill you just missed will cost you the match are about 99%.
Long, flashy combos in general get this, as the game's engine actually decreases the damage of each hit the longer a combo gets.
The IKs in the original Guilty Gear was a game breaker in that a successful hit not only won that round, it won the match and were easy to do, so IKs got nerfed HARD.
Guilty Gear's spiritual successor BlazBlue has this in Astral Heats: gigantic finishing moves that guarantee victory if they connect. Unfortunately, in order to do one it has to be the absolute last round of a match, with the opponent's health at 20% or less, and enough "MP" saved up for two regular specials. Since you only get one shot, it's usually much wiser to just use the two specials. Continuum Shift made it a bit more usable by making several Astrals more comboable, needing only the match point round and only 35% health, but it also now requires a Burst stock. In high-level play Burst stocks are absolutely necessary for character specific combos as well as getting out of very long combos from characters like Litchi, Noel, Taokaka, and Jin. This can, like Guilty Gear, end up majorly biting you in the ass and costing you the match.
Ragna's Devoured By Darkness Distortion Drive is this so much. It's one of the most powerful moves and regenerates a massive amount of health. However, to even use it you need to first burn 50% heat to activate Blood Kain, before burning another 50% to use the move itself. With Blood Kain's aura, never mind the activation animation, it serves as a big Harbinger of Asskicking that tells sensible players to stay away. It does not help either that Devoured By Darkness is a grab, with the poor range that implies, and Ragna does not have a You Will Not Evade Me move to drag the enemy into reach. All these combine to ensure that it almost never shows up in serious play.
Ragna can use use it to finish a combo. If used before there's too much damage reduction, it guarantees a victory.
Any special move that requires the opponent to hit you to trigger it, as it requires nigh-meticulous timing to pull off.. A prime offender is Fei Long's Gekirinken from Street Fighter IV. One of the most awesome special sequences you'll ever see in fighting games, but the opponent has to hit you to activate it. And if it fails, you lose your Ultra gauge completely. Not to mention, like all Ultras, it begins with a long flashy ultra move animation where Fei yells "Attack me from any direction you like!", ensuring that you'll only succeed in countering newbies who don't yet know it's a counter stance, or fighters who are already in the middle of their attack animation.
The famous Za Warudo combo in JoJo's Bizarre Adventure fighting games, seen here. With the steamroller itself, anyone can see it coming a mile away, it's blockable, Dio can be punished as he jumps up to perform it, and the damage really isn't worth it. But who can resist recreating one of the most badass and infamous moments of the entire series? The Time Stop in the game can be a pain too, since you're immobile for about 3 seconds before executing them and unless you set your SC gauge at max every round, you will have limited time to actually set up your combo. However, enjoy your 66-hit combo if you pull it off.
The same also goes for Jotaro, Dio and Shadow Dio's Time Stop supers. They require a lot of energy to get the most out of and they have a huge windup time and complicated button inputs but done right, they can help you deal incredible damage.
Giorno and Kars's transformation GHAs in Jojos Bizarre Adventure All Star Battle. While both offer new moves, properties, and very increased mobility, it's far too easy for a player to shoot themselves in the foot if their opponent smacks them before the transformation is finished. To this end both characters have a move or two devoted to creating space in order to transform safely; Kars has a kick that sends the opponent to the other side of the screen, and Griono's HHA does the same while also briefly stunning the opponent afterward. Pucci averts this by his entire moveset being geared around getting to Made in Heaven, and Made in Heaven itself being completely worth the investment.
Mortal Kombat brutalities. Flashy, incredibly nasty ways to finish your foes. Also nearly impossible to pull off. They require a sequence of button presses that's near impossible to actually do. It's awe inspiring to see it pulled off though.
For that matter, uppercutting. Takes away one quarter of your opponent's life, tosses them up in the air, and they make a cool screaming sound, but it's really hard to hit anyone with it, unless it's the first battle in the one-player game. It's also impossible for most characters to combo with the move because of the forced delay on a successful hit.
For that matter, Fatalities in general. Sure, the franchise pretty much invented the concept, and they are really cool, but in most games, they and the other finishing moves provide no real tangible benefit to gameplay. The first game was an exception; you did gain bonus points for doing them, and it was also a requirement for unlocking Reptile. (For some time, that meant Sonya could not fight him, because her Fatality required the Block command, which disqualified her due to another requirement. This was fixed in a later edition of the arcade game and all home ports.) Ironically, in many other fighting games that have similar moves (like say, the Soul Series) there is a tangible benefit for doing the finishing moves.
X-Ray attacks in Mortal Kombat 9 are insanely gruesome, showing your opponent's bones breaking and organs rupturing in slow-mo from the brutal strikes. They also require 3 bars of meter, which is also used for the extremely important and useful breaker move. On top of that, many of them are slow and easily blocked or dodged, meaning that the only way to reliably land them was to use them mid-combo, where damage scaling severely reduces their damage.
The Special or Ultimate attacks from Naruto Clash of Ninja are like this. Especially in the first game the attacks generally did a fair bit of damage but cost all of your charge bar. They're also pathetically easy to dodge, making them all but useless against human opponents.
Many fighting games have super moves that do insane amounts of damage and are awesome to watch, but often require button combinations that only Mr. Fantastic can pull off. One example is Ivy from Soulcalibur, whose telekinetic attack Summon Suffering is absolutely amazing, but the human players who can pull it off can probably be counted on one hand. Not that you won't see it often, but it won't be against a human.
There is actually an Achievement for pulling this off in Soul Calibur 4.
There's also Talim's "Whirlwind Festival" throw: a multi-part, highly damaging throw that requires you to input very odd button combinations in very short windows of time as she performs the throw. Fail, and the throw's animation cuts off at the first part or so and does far less damage.
More on the 'impractical' than 'difficult' side, Taki has a whole repertoire of attacks that involve attacking from the air, complete with screen-darkening and light-burst effects, but which almost never connect because their pinpoint aiming accuracy means that an opponent can dodge them by taking a step to the side.
Another example from the Soul Series are the unblockable attacks. These attacks are powerful and unblockable, but are so painfully slow that even inexperienced players can easily sidestep or dodge them, and counterattack with impunity. Starting and then canceling these to psych out the enemy is the only real use for them.
A few unblockables have some use by having unexpected properties, like Raphael's surprisingly long-ranged unblockable lunge. Of course, once an opponent knows what to watch for, they're worthless.
Unblockable and projectile attacks in many 3D fighters tend to be this way. It's hard to carry the legacy of the Dragon Punch or Hadoken when the opponent can sidestep.
Zombie pirate Cervantes has the unblockable projectile where he shoots the enemy, but he flourishes the gun for so long that only the most idiotic of opponents won't either evade or duck it.
Several of the Final Smash attacks in Super Smash Bros.. Brawl qualify for this trope. For example, Meta Knight, who can get off attacks frighteningly quickly and has decent smashes, gets a fairly weak Final Smash, which is hard to hit with, but if it connects with one character, it will hit everyone. Jigglypuff probably has the most ridiculous example of this trope, with a Final Smash that has such a long startup time that it almost never hits and is useless on large stages. However, if it connects, it kills.
Donkey Kong's Final Smash, a rhythm minigame with a soundwave that grows bigger with each beat that's hit correctly. It takes some time to get its range to anything useful and has no outstanding damage output or knockback. It was even worse in Brawl, where the player wasn't even given a button prompt, so it ended up being luck more than anything.
Then there's Captain Falcon's famous signature move, the Falcon Punch. One of the most damaging attacks in any of the games, but its charge-up time not only makes it extremely difficult to land a hit without proper planning and/or a bit of luck, but it also leaves him wide for attack. Needless to say, when someone does manage to pull it off, it can only bedescribed as awesome.
The true Awesome, But Impractical move is the reverse Falcon Punch and its stronger variation, the reverse Warlock Punch. These moves require that you turn at just the right frame to make Cpt. Falcon or Gannondorf spin around and punch the opponent. Amazing when successfully pulled off, but it takes even longer to get to the actual punch and only does slightly more damage.
Ice Climber chaingrabs border on this. Having the ability to infinite-combo anyone makes them incredibly annoying to play against (the strategy for dealing with them is "don't get grabbed and separate them whenever possible"). Actually pulling it off requires a long series of two-frame timings that vary based on the weight of the chaingrabbed character, having both Climbers close enough together that your opponent can't escape before you begin the throws, not being interrupted by an environmental hazard or other player as applicable, and, because everyone knows that one grab is death, having to deal with the aforementioned strategy just to get the grab in the first place. Granted, it's somewhat easier than it sounds. The "impractical" part comes in when you factor in the mental fortitude required to pull it off two or three times per game (they still can get kills the old-fashioned way, after all)... two or three games per set... and, depending on the size of the tournament, 7+ sets per tournament. And you could have put all the practice you did into Meta Knight instead.
The Home-Run Bat became this in Brawl. In the original and Melee, it had a slightly unwieldy swing time but was still usable much of the time and was a reliable one-hit KO, stage obstacles notwithstanding. In Brawl, it works more-or-less the same way, except the swing time is dramatically increased. It makes a highly effective thrown weapon in every single game, however.
Some hyper combos in Tatsunoko vs. Capcom are Awesome, But Impractical. Ippatsuman has it the worst, though. His Level 3 Hyper Combo has him summon his signature robot for the player to control. However, all of its attacks are slow, don't do much damage, and can't combo past two hits. He does have 3 unblockable moves in the mode, two of them being telegraphed sword attacks and a humongous unavoidable flash that kills the opponent if it connects. The problem with the flash, however is that it takes ten seconds even hit with the move, the most time of any non-boss character inCapcom vs. Whatever history and can be interrupted by attacking the robot's head during the charging sequence. Finally, in the international release, the time the robot stays on the field decreased by a large margin and ate every bit of meter when used.
Valentine's "Forbidden Procedure: Rebirth Ex Machina" can revive an ally or damage an enemy, but the damage is not that much better than her level 1 Blockbusters, and the meter would generally be better used towards finishing your enemy since by the time you gain 5 bars and have a dead character, the fight should be nearing its end.
Double's "Megalith Array" has her turn into a Bullet Hell spewing Moai statue which can potentially deal insane damage, but she turns back if she takes even a single hit, potentially wasting your entire stock of meter if timed improperly or if your enemy is skilled enough, which makes the move Difficult but Awesome.
Big Band's level 5 is confusing to pull off, requiring you to play the game's opening Leitmotif on his trumpet, which leaves you wide open to attack since only the first honk will hit an enemy. And to make matter worse, Big Band's level 5 itself doesn't do any damage, but rather stops time for the sake of giving you time to play without being attacked, or use to launch Satchmo Deathblow, which can be used much easier by taunting the enemy and then hitting with his ground level 1.
In the third installment of the Final Fight franchise, the enemy named Hunter has an extremely powerful overhead strike using the baseball bat strapped to his back that takes out 80% of your health in a single hit. Impressive for a garden variety mook. However, the attack has a long delay before he unleashes it, so it's easily avoided or interrupted merely by hitting him.
Deathblows in Mutation Nation. Basically a screen-filling Smart Bomb that severely damages all enemies and knocks down the ones that manage to survive, which come in four flavors of varying power (with the most powerful one actually being able to hit some enemies twice.) However, it requires the player to stand still for almost 2 seconds to charge it, and the charge is interrupted when you're hit. And while you can hold the charge after you've finished and even move around while doing it, it doesn't last long before you'll lose it and have to charge up again. Effective against bosses and mid-bosses, but when you're dealing with large groups of enemies it's too difficult to pull it off for it to be worth it (and the lower damage ones like the Spirit Attack just aren't worth it, period.)