Ace!Sam, so hopelessly in love with Jess. Jess and Sam having sex a few times early on (cause "that's what couples do"), but pretty soon just not bothering anymore since neither of them's all that into it anyway. Sam and Jess both being blissfully happy with their relationship, exactly the way it is – but going along with it when their friends assume they screw each other senseless, cause they don't think anyone would understand. Sam and Jess stifling giggles as they make loud, obscene noises from just behind their closed bedroom door, purely to mess with their roommates.
Ace!Sam feeling occasional romantic attractions, and wishing the circumstances of his life were different so he could pursue a relationship, but also feeling a little relieved that they aren't, since he doubts anyone would really want to be with someone so indifferent toward sex anyway.
Demon blood giving Ace!Sam a carnal drive to claim and possess. Sam being scared and kind of disgusted by the things it makes him want to do, but wondering if these are the kind of "normal" sexual desires he's "supposed" to have. Sam trying to convince himself that this is a silver lining, cause maybe he'll get used to it and when the whole apocalypse thing is over he'll get to be "normal" after all.
Ace!Soulless!Sam trying to convince himself that his "problem" with sex was probably just some sort of overdeveloped moral hangup, and that he could be interested in it now that he doesn't care about that kind of thing. Soulless!Sam picking up random partners, experimenting with anything he can convince them to go along with, and callously suggesting it's their "fault" when he still just can't get into it. Sam getting his soul back (and with it, his morals), and hating himself for having treated anyone so horribly.
Ace!Sam despairing of ever being "normal," or alternatively, of ever again having a relationship like he had with Jess.
Ace!Sam developing feelings for Gabriel, but figuring a rake like Gabe would never want to be with... well, someone like him. Gabriel reminding Sam that, while he does enjoy sex as one of the things he can do since taking on corporeal form, in his true form he doesn't even have sex organs – so he hardly considers sex an essential part of himself or his relationships.
Gabriel loving to stroke Sam's hair as they cuddle together in bed, and Sam loving to stroke Gabriel's wings. Sam and Gabriel both liking to make loud, obscene noises from just behind their bedroom door, purely to mess with Dean.
Gabriel mentioning in passing one day that, if Sam's interested, they could try gracebonding with his grace and Sam's soul – angels' highest expression of intimacy, which is sometimes called "grace sex" but is honestly nothing of the sort. Sam deciding to give it a try and discovering that, for him and Gabe and their relationship, it's absolutely perfect.