In my opinion, I don't really think sex scenes are necessary. And if they are included, if they're not done right, they are really difficult to take seriously. Throw in the possibility of modding, and you get all kinds of ridiculous 'skimpy' this and 'doggy style' that kinds of mods that sometimes make one roll one's eyes.

That being said; IF sex in a game is done well, it can be enjoyable. A good example in my mind is Dragon Age: Origins. A bad example would be its sequel, Dragon Age II.

Also, adding in sex to a video game can be a controversial decision. If you decide to budget the game more tightly by adding only heterosexual encounters, all the gays (myself included) get a little bit irritated by this, especially if the game company has a history of including homoerotic experiences in past games (see BioWare). If the game company decides to be inclusive, and add homosexual experiences as well, they run the risk of alienating the more conservative fan base.

If sex is done well, it can enhance RPG video games greatly, and can be seen as a reward for complex interactions and play choices with certain NPCs. If done poorly, it makes the game laughable. But in the end, I can take it or leave it. If I was forced to choose between sex and no sex in all future games though, I'd probably pick no sex since it's the least risky option for a potentially enjoyable game.