Very annoying, not so much for anything they do so much as for the volume of useless traffic they generate. Some examples:
The latest, the Nimbus, is particularly nasty in the way that it tries to exploit 13 separate (but related) vulnerabilities in
MicroSoft server software. This means that each attack causes 13x the load that each Code Red attack caused. Patches exist but it seems like many people don't bother to install them either out of ignorance or apathy. Since the machine the worm last came from is clearly identified in any attack, it should be a relatively simple matter for someone (i.e.
MicroSoft) to keep a list of infected machines and automatically email notices and patches (only once per machine of course).
It seems to me that the majority of worms use BufferOverflow. This allows an attacker to submit input to a system that causes the system to run malicious code.
Note: although malicious code (in particular, the worms mentioned above) is reportedly spread mainly by email, it is still possible to be infected merely in the course of normal web-surfing.
See also HumanVectoredScriptingWorm