I'd say it depends on the game, but mostly my answer is no.
See, the reason I don't think sex scenes (I mean sex scenes like, frontal nudity) should be in most games is that they are distracting. Pretty much every male who sees his character having sexual relations with another character will be more interested in the fact that they're having sex, and less on the fact that it might have extreme story implications. The scene will act as a distraction from the very story it's attempting to forward.
I think the best way for characters to have sex, without distracting from the story, is to simply imply sex. Mass Effect seems to have achieved this effect (I think, as I haven't played the game, so I'm working off hearsay here) pretty well by having a darkened scene and only showing a butt for a second or two. I'd say that's the way to go for most games. Some games, though, would probably benefit more from nothing past a dark screen after a kiss or maybe even just a passionate hug in a sexually prone area (like a bedroom, especially if the characters are lying in a bed).
A type of game where a sex scene, or even just frontal nudity, would be more appropriate is a game that doesn't try to forward a story with it. It should obviously be very optional and off-the-path, so more seriously geared gamers can get on with the game, but people looking for a bit of sexual arousal can get it if they want.
Off-topic:
I also feel this thread would be a good place to put my thoughts on another aspect of sex in computer-generated media: the "how can you find a bunch of data and pixels attractive" argument. In an attempt to try and show why this argument has no merit, I say: we can think data and pixels are attractive because they are a representation of something that is real, and the human brain is geared to translate even the most obscure things into something recognizable. Translating something that attempts to translate itself anyway just makes it easier. We can find data and pixels attractive for the same reason we can find data and pixels to be frightening or even be able to recognize a human NPC in an average video game.
I just really think it's annoying when people try to say this to people who say things like "Alyx is hot" or such, so I wanted to say that.