That's interesting. I wouldn't describe adding a sharp as creating or releasing tension, it is more a buildup of energy.
It is 'tension' while we remain in the same key. For example playing a C# when in G suggests to the ear that you should move to the D row (you probably have, to get it!) Basically you are hinting a dominant A7 chord rather than the modal Am chord in the G scale. Now where?
- Keep our tune in G and have that C# as a tense note (you've gone 'Lydian') - back to our tune - relax = "improvisation"
- Move temporarily to the D row and then relax back - commonly via a D C# C (B) chromatic run A favourite Shand trick - deeply melodeonic too!
- Switch tunes altogether but move to the new key.
While there is a sense of energy in playing in D - I'd say the 'tension' exists as the C# clashes within the G key centre (these notes being 3 tones apart) and is dissipated when you actually switch key. Thereafter you are 'simply in D' and must look for different sources of tension - dare I suggest a C natural note
Interestingly we folkies tend to move G to D to A. Country and Westerners do the same thing but move 2 sharps at a time - you'll hear a lot of G straight to A in Nashville. To some extent both are part of the idiom. Arguably cliche - but then so is II V I in jazz?