I've played a few demos but the most memorable has to be Return to Castle Wolfenstein. Before the game was released there was a multiplayer demo of a part from the game where a fort on a beach is being seized by US forces and defended by Nazis. I played the hell out of the demo, even after the actual game came out.

Stanley's Parable

That's what I came here to say. That first level was the best anyway.

Maybe this impression is just a subjective thing? I played through the whole game and loved nearly everything of it.

If I remember rightly that's actually a whole different thing to the actual game (but at the same time it's exactly the same thing for precisely that reason).

Correct. The demo works the same way the "game" does but is a complete standalone experience with a whole lot of fun. Often you play a demo to get a feel of what the game really will be. In this case you will not really be wiser after playing it but still you want to buy the game even more. It's just strange.

Egad! you traitor!
You must now hug three strangers and pour maple syrup over your next meal to be absolved of your sins, my son.

Tropico (1)

You have been forgiven, my son. Now go in peace, and forget not to keep thy stick on the ice.

One of them is Rise of Nations. I may have had more fun with the demo than the game itself. The music was great, the art and portraits really looked great to me, the simple discovery of what all was in tat demo was astonishing.

Age of Empires III was more or less the same, except not as fun as RoN. However, that demo was quite excellent and it had one of the better campaign missions anyway.

Call of Duty 4 was pretty good as a PC demo. I never played the full thing there, the Call of Duty demos were also quite good and I enjoyed playing them a bit.

Crysis also had a fun demo that I am pretty sure nearly killed my computer (it could have been a safer site, but the demo was kinda buggy too).

Close Combat: A Bridge Too Far was a completely awesome demo that I really liked to play. That was when Microsoft games had great previews (Flight Simulator '98 I think this was) such as the Age of Empires preview (which was a better advertisement than the game it represented) and the Close Combat: ABTF preview as well. This demo was pretty good, as well as III and IV's demos.

Normally 'trying to be clever' is a bad thing. But not when it's a rousing success like that was.

Imperium Galactica II had a terrific demo as well. Much like HoMM3's demo, it provided a good hour of clean fun until the AI found me and laid waste to my strangely useless army :P