I will not promote or condone the use of drugs with sex, I can only tell you from my own experience, the testimonials of people I know and my knowledge of the biological effects that drugs cause. As an adult you have the right to make your own decisions and mistakes.
It depends on the person and the purity and quantity of the drug in question. Any drug in excess will make the sex worse if not physically impossible. Know your limits and as with any sex you should always use at least one form of protection.
Intravenous drugs (injected drugs) can give you Aids and other STDs if you share a needle and they are usually very dangerous, Stay clear of anything like that and people who do them. You can also catch Aids doing cocaine sharing a bank note if any blood gets on it.
Alcohol will increase sex drive and reduce inhibitions but dramatically decrease sensitivity and decrease coordination. For most men this is can be beneficial but too much and they are going to fail to get it up. I don't think I need to comment on the long term effects of alcohol are well know.
cocaine will give you more energy, increase blood flow and make it feel a lot more intense. I don't know it's effect on women sexually but for men it feels really good but make us last inconsiderably longer. It make you feel more in tune and in the moment. You can get mild comedowns and it is very addictive. It is also VERY expensive and excessive use can cause brain damage and make you violent.
Speed has much the same effect as cocaine but more physical than mental. It give a raging boner but there is a very fine line between really good and too much. It can make ejaculation near impossible. It is very addictive and can cause very bad comedowns.
Acid (LSD) is simply too hallucinogenic and makes getting it up impossible and makes it too hard to concentrate. Bad trips can cause mental problems that never go away.
MDMA (the chemical that is SUPPOSED to be in pills) Has similar effects to speed and cocaine but also greatly increases sensitivity and makes it far easier for a woman to achieve orgasm. In men it makes it harder to get it up but when they do their erection is harder, thicker and longer than usual. MDMA can cause an absolutly horrific comedown though which can make you feel like crap for days. It is also highly addictive, can cause severe depression and damages the brain.
Weed can make sex feel really good, it generally makes you feel more relaxed and in tune with your partner. It seems to increase the sex drive of women dramatically and they seem to achieve orgasm more easily and lets them let out their submissive side. I enjoy it but personally I prefer to feel more aware and in control. Weed can cause some people long term mental problems if used in excess but in moderation I think it is FAR less harmful than legal drugs such as alcohol, caffeine and nicotine.
Viagra should not be taken at all unless the man has erectile dysfunction. I once took half a tablet to enhance sex and it was too much.
