I wrote about this in the other thread, too. With real drums, different head tension changes a lot more than just the pitch of the drum. The interaction of the tension of the head and the resonant frequency of the shell and the room can make a real difference in the overall envelope and dynamic of the drum itself. Tighter heads tend to ring more true, while looser heads are more like noise. Especially when you start messing with tuning the top heads different from the bottom heads, it gives you all kinds of options for how the drums come through.
None of that happens when you adjust the tuning parameters in SuperiorDrummer. All it really does is play the sample back faster or slower depending on your settings. This also shifts the whole frequency response of the room and mics involved by the same factor. It also changes the "size" of the room, by changing the timing of reflections. You can almost get away with it, if you're not going very far, and especially if you're tuning the entire kit the same amount. I personally can hear the difference in even small adjustments, to the point where if one drum is tuned different from the others, it sticks out to me as noticeably different.
Even without all of that, I don't think I would do much of matching the pitch of the drums to the key of a song. I just pick drums that sound right in context and kind of just go with it, or I use extreme tunings for special effects. Maybe I would experiment a bit if it didn't change everything else. Maybe I should, but I don't.