Knoppix seems to be the most popular liveCD distro. And some others are actually knoppix based. It's a good way for linux starters to get the feel on how's it inside linux though these are often much less featureful than the full versions you install on the disk.
I tried Damn Small Linux, only about 60MB to download and it's nice, but i don't see myself doing anything too productive with it.
However, i think it's a very good thing for being able to do something like programming or learning programming, playing music, watching videos or something if you haven't a laptop. You just carry a CD, enter it in any computer you come by and do what you want and can do with it. It's a very neat thing.