Best Level

[Fragony]

Pretty much all the levels on Thief: The Dark Project.
Return to the Cathedral and Down in the Bonehorde are most memorable.

TRUTH

My favorite is assasins though, I like the missions where you have to travel to your destination, really missed that in part deux. Only life at the party lives up to the first.

[doc_bean]

The Haunted mansion from Vampire: Bloodlines. Most of Prince of Persia: Sands of Time (the library especially stands out).

Some of the most frustrating design was imo found in God of War, tedious 'platforming' sections don't belong in an action game. Most of the game is okay though.

[TinCow]

The memorable ones for me are often those that are 'artistically' great and which really make me feel at home in the game, rather than any particular layout or difficulty. Here are a few off the top of my head:

Unreal - The first level (exiting the ship). That moment when you finally get outdoors, see the massive and beautiful (at the time) environment, and the music swells.

Half-Life - When you are crawling through a tube which empties out in the middle of a verticle cliff face with nowhere to hide and helicopters buzzing around you.

Starcraft - The first time you play the zergling rush level early on in the Terran campaign. No one ever really prepared well enough for the size of the rush the first time they played the game.

Medal of Honor: Allied Assault - D-day landing map. These things have become clichéd with the flood of WW2 FPS games, but this was one of the first to do it really, really well.

Doom 3 - Escaping from the underground tunnels area right after everything goes to hell (literally). You've only got a pistol, minimal ammo, and everything that can go wrong, does.

TIE Fighter - The mission when you are betrayed, forcing you to destroy a minefield with an unshielded TIE Interceptor. Ridiculously hard and it makes you feel like you actually WERE meant to die. The relief at finally surviving is immense.

X-Wing Alliance - The Battle of Endor - All of the missions in the Millenium Falcon. They capture the thrill of the battle in the movie so well. Next time you're looking for a challenge, play the final mission where you fly through the Deathstar, but do it like the movie: dump all power to your engines and fly like your pants are on fire. Incredibly hard and exciting at the same time.

Baldur's Gate 2: Throne of Bhaal - Sendai's Lair. You slaughter massive hordes of enemies while being treated the cutscenes of Sendai totally flipping out. This area really made you feel like you actually were becoming a god.

American McGee's Alice - The 'garden' level where you shrink to the size of a bug.

[Rodion Romanovich]

It's too difficult to make a true list of all great objective based mp maps or singleplayer maps, so I'll just answer an easier question: favorite deathmatch mp maps:

UT: Morpheus (low gravity, redeemer, sniper rifle, rockets - what's not to like?) and Morbias (a simple cylinder shaped level with an upper circular balcony which you can reach through a few lifts, and nothing but rocket launchers!) in the original UT
UT2004: Morpheus3, the UT Morbias clone (forgot its name), Serpentine (once you gain the high ground the enemy is screwed for several minutes if you're clever )
Q3: The longest yard (nowhere to run, nowhere to hide... )

[Stig]

Knights and Merchants, levels 4, 8 (or 9, I think I forgot ... for those of you who had this fun game, it's the one where you start with a magazine and 2 groups of Barbarians and a group of Crossbowmen), 15 and 16

[Husar]

Knights and Merchants, levels 4, 8 (or 9, I think I forgot ... for those of you who had this fun game, it's the one where you start with a magazine and 2 groups of Barbarians and a group of Crossbowmen), 15 and 16

That was a fun game, level 7 IIRC was really hard, you had to fight some barbarians rather early and had only a few men. Very tactical game, a bit like Total war actually, concerning the battles and their calculations. Just a pity that the stand-alone expansion is very buggy and keeps crashing. Though I don't remember it having some exceptionally great levels.

I might add mafia, for the same reason that I added OFP, big level, lots of freedom.

[Kekvit Irae]

UT: Morpheus (low gravity, redeemer, sniper rifle, rockets - what's not to like?) and Morbias (a simple cylinder shaped level with an upper circular balcony which you can reach through a few lifts, and nothing but rocket launchers!) in the original UT

Meh, give me a good CTF on Lava Giant or Facing Worlds, and I'm happy.

[Rodion Romanovich]

Meh, give me a good CTF on Lava Giant or Facing Worlds, and I'm happy.

Yes, UT Facing worlds (and Q3 space ctf) are probably my favorite CTF levels However the bots in the original UT were too bad snipers, so in singleplayer I prefer the UT2004 Facing worlds!

[Geoffrey S]

TIE Fighter - The mission when you are betrayed, forcing you to destroy a minefield with an unshielded TIE Interceptor. Ridiculously hard and it makes you feel like you actually WERE meant to die. The relief at finally surviving is immense.

That one's great. Harkov I think it was. Really well done, and the buildup in earlier parts through cutscenes and the secret missions was perfect.

A favourite bit is in Timesplitters: Future Perfect, where you keep entering a time portal in the same room and end up with four of 'you', and each time you step through you have to perform a different task while the other you's do what you did earlier. Awesome.

All-time favourite? Facility, in Goldeneye 64. That was FPS perfection.

[Decker]

All-time favourite? Facility, in Goldeneye 64. That was FPS perfection.

That was one brilliant level to play. I especially love the opening "first shot."
I also like the one where you fight your way through the cemetery only to get shot by Alec. That was one of my favs to play.

[Xehh II]

Any level from Thief Gold, my favorite being Song of the caverns because of of the guy at the end of the cave that sings "Go go go, up, up the rope you go, while you creep so sloooooowww".
But I hate the Maw of Chaos, How the hell do you beat the Trickster? I don't get anywhere near the eye to replace it.
I also hate the third level, where you have to find the horn of Quintus, and I also hate the level where you have to go though the abandoned part of the city to the Hammer Church, and I also hate the Hammer Cathedral, man, those Undead Hammers are way to powerful.

[Kekvit Irae]

But I hate the Maw of Chaos, How the hell do you beat the Trickster? I don't get anywhere near the eye to replace it.

Spoiler Alert, click show to read: 

Moss Arrows. Fire one near you, and another close to the trickster. Sneak up behind him, and replace the eye.