Featuring the Badass Bookworm as a technically gifted guitarist pretty much for the hell of it. Most awesome thing in the history of anime? Quite possibly.
The version from the English dub is also really cool. Basically, they just translated the lyrics and left it at that rather than trying to write a new song.
Also, Tchaikovsky's Symphony #4 Movement 4 from the climax of "Day of Sagittarius III" and Mahler's Symphony #8 Part 1 from the climax of the first season. Both of them are also awesome outside of this context, naturally, but this is where they accompanied Crowning Moments of Awesome.
What about the song that played during the battle? Shostakovich Symphony No. 7 'Leningrad', The Invasion?
One of the best songs in the soundtrack is also one of the simplest: Itsumo no Fuukei, which translates to "Everyday's View". It's known for being that piece of music heard when Kyon talks about no longer believing in aliens, time travelers, and espers in the first episode (in chronological order; it's the second episode in the other order).
What about Oi Oi? It's the "What has Haruhi got us into this time?" music.
Let's make a quick mention of the Nagato Vs. Asakura music, which is absolutely fucking badass AWESOME as background track.
Another awesome battle soundtrack: Kamadouma (the Cave Cricket battle music).
The new opening of the second season, "Super Driver". Fuck yeah.
The OP and ED are the OP and ED for a reason. You cannot deny the opening bells of Bouken Desho Desho? nor have you not sprained something trying to dance to Hare Hare Yukai.
Understandably overshadowed by those two songs, but Tomare!, the second season's ending, is awesome in its own right as well.
The movie, of course, has a Crowning Soundtrack Of Awesome.
The movie's theme-song Tender Oblivion. Granted, it's not so much "awesome" as it is "extremely touching", but a song this good needs to be aknowledged, dammit!
Personally, the 'Sonority' version of Gentle Oblivion is far more powerful. The song is an absolute Tear Jerker of a song, but this rendition just cranks that Up to Eleven. It's the least busy out of the renditions, and all that is left is the beautiful singing itself. If this doesn't move you in some way, you have no soul.
And of course not to mention one particularly memorable track, there's Ready, the song that plays while Kyon decides if he wants to try and restore the old timeline or not.
First Good-Bye. Originally from the last episode of the radio drama but re-released as part of the "Imaginary ENOZ" LP. In This Troper's opinion, easily keeps up with God Knows and Lost My Music.
Suzumiya Haruhi no Gensou is essentially a concert of songs from the anime turned into classical music (and also very, very awesome). A few songs have already been mentioned on this page, but listen to the version of Lost My Music to be completely blown away.
Itsuki Koizumi's version of Hare Hare Yukai was so good, this troper covered it!
The Russian versions of the first season's songs are quite amazing too.