Nowadays, MODs and sprites have been replaced by MP3s and OpenGL models, but the demoscene lives. Check out Tranzmit for downloads of a whole gaggle of brand-new demos in the classic style. They are often breathtaking, but somehow, I remember the orginals as even better.
Speaking of cool graphics, check out this video from made from the hot in-development game Halo. It's a GeForce2 GTS ad, believe it or not. Pretty funny.