They're pretty common if you get late enough and don't get banned (they can easily be banned by the UN if someone chooses to, and the AI generally takes its sweet time to build the Manhattan Project, so unless you build it, it might take awhile). They're definitely effective, although you can quickly trash your relations with much of the world since you take a diplo hit with the civ you nuked along with all of their friends. But who needs friends when you can force civs to capitulate just from a barrage of nukes? (The easiest way to speed up a conquest/domination victory)
The only annoying thing is that the AI can be pretty reckless with them. They'll fire off tons of nukes without really considering the consequences, so before you know it half the world is a desert and everyone hates each other. Oh well.