My best demo would be Prey. Mainly because it's better than the full game.
Prey is deliberately designed so that all the cool things are shown/experienced to/by the player within the first hour of playing (the bar, the abduction with the Blue Öyster Cult music, seeing a portal for the first time, the kids, the planet-in-a-jar). Coincidentally, that's how far the demo for the game goes. Once you're past the part where the demo ends, the game becomes just another boring FPS game that ends a couple of hours later.
Prey is currently clogging up every video game bargain bin in my country, but it's still not worth the price.
The only other demo worth mentioning is the one for Space Quest 6, because it's an independent game in itself, where Roger Wilco has to deal with his ship being attacked by the Bjorn Collective.