The BBC documentary The Life of Buddha describes how Prince Siddhartha abandoned what many religions and individuals would consider the ideal sexual relationship to pursue his religious vision. When he left the palace on his route to enlightenment, the prince was already married, and his wife had just given birth to a child. Sex within marriage for the procreation of children is sanctioned by every religion and state system of morality. This, however, is precisely what the Buddha left behind.
The other short clip from the Clear Vision Trust gives some theological context to this decision. The speaker discusses sex as a form of attachment. The difficulty with sex for Buddhists is not that it is prohibited in particular forms or situations, but that it arouses strong emotions, which will necessarily be a cause of pain. Both sources therefore suggest that it is better to abstain from sex, since it is ultimately a source of suffering. Sex, like other forms of attachment, is not good in itself, as it presents a barrier to enlightenment and a source of pain. The less it does this, however, the better (or less harmful) it will be.
From this one can surmise that the best type of sex would be sex undertaken for the benefit of another. If someone is strongly attracted to you, and you are indifferent to them, it might be an act of kindness to engage in sex. Similarly, if a woman wished very much to conceive a child, the man who helped her by having sex with her would be performing an act of kindness. The best motivation for anything, according to Buddhists, is benevolence, rather than desire.