As long as they have the components, the caster shouldn't experience the adverse effects from those dead under a year, correct. 

For missing body parts, there is no stipulation to when they went missing, so by the text yes they would get them back. But if you are the DM, and your PC wouod like to keep their prosthetic, you can flavor that however you'd like! Dnd is meant to be built on. 

How rare is this?
I'm wondering because one of my characters' backstory is based around his obsession with wanting to bring his dead wife back to life (and never succeeding). It might dumb if there are people out there that just have this power? 

Well I mean, just because this spell exists doesn't mean it's widely available. Plus it has a really expensive material component and is taxing to cast, so it's not exactly a one and done deal. Could be that most don't use this spell for these reasons.

If a character had transitioning surgery, does this spell reverse it?

Quote from SparingAxis >>

RAW, if the target is dead for less than a year, this spell can be cast multiple times without the caster suffering negative effects, no? I will rule that way, but curious how others feel. 

In addition, would this replace a limb that was lost before, and has since been fitted with a prosthetic?  What if the PC wants to keep the prosthetic? 

 Unfortunately for them, if they want to keep the prosthetic, they're going to have to chop their regrown limb off and place the prosthetic back on.

The spell says, "This spell closes all mortal wounds and restores any missing body parts."

It doesn't say, "This spell resurrects people but like only the parts you want to resurrect."

Quote from SparingAxis >>

RAW, if the target is dead for less than a year, this spell can be cast multiple times without the caster suffering negative effects, no? I will rule that way, but curious how others feel. 

In addition, would this replace a limb that was lost before, and has since been fitted with a prosthetic?  What if the PC wants to keep the prosthetic? 

Correct. The target always suffers from the effects of resurrection sickness, but the caster only suffers ill effects if the target has been dead for more than a year.

I see no reason to deny your player's character fantasy here. If the prosthetic is important to the character, just handwave it as the soul's sense of self shaping the regeneration process as it returns to its body. 

Quote from DnDcentral >>

If a character had transitioning surgery, does this spell reverse it?

Same deal here, but more emphatically since I'd assume that the character's gender identity is pretty fundamental to their story. Your interpretation offers some interesting roleplaying opportunities, but check with your player before springing something like this on them. You could very easily apply the same logic of the soul's sense of self dictating how the body regenerates. Heck, you could take it a step further and actually have the character regenerate with their sex matching their gender.

Both of these cases that are very easy to accommodate as a DM since you can literally make up the rules. Don't let mechanical minutiae get in the way of an enjoyable game.

Does this prevent ceremorphsis?

Imagine killing one of your own party members, cutting off their finger or a piece of skin, go into a place, pull it out of your pocket and be like "Sorcerer, I choose you!" and cast this spell. Sure, they'll be naked, but it'd be quite intimidating. It'd be even better if they have good spells that don't need material components at the ready.

How do I add the resurrection penalty to my rolls? As of now I've been raw mathing it but both the DM and i forget every now and then that I've got it.

it doesn't have to be one single Dimond. 

Quote from thetruemeerkatking >>

it doesn't have to be one single Dimond. 

 Yes, it does. The text is very explicit, "a diamond worth at least 1,000 gp." There is no way to read "a diamond" as "multiple diamonds."

If a creature was undead for longer then 100 years but is now really dead, would this spell work or not? If so, would they be undead or would they be restored to normal life? 

Depends on if they used magic to transition or not. I'd say that while there's no spell that technically has the effect it's probably easier to use magic for that in most settings, since most settings don't exactly have the medical technology to do said surgery without it. But I'd say it stays if it's a magical transition, and is reversed if it's non-magical. 

Quote from Suspiciousslime23372 >>

Does this prevent ceremorphsis?

 Not in BG3, but in the actual rules I'd say if you physically removed the tadpole before casting it wouldn't come back. Otherwise, I'd count it as a magical disease and not remove it.