The alpha value in the backbuffer does not affect what is scanned out to the screen.
However it does affect blending. For example, non-premultiplied alpha blending is:
output = source * sourceAlpha + destination * (1-sourceAlpha)
While the above uses the source alpha only (giving you the "transparency" effect you expect), you can also set up the blending stage to use the destination alpha. Most sane blend modes only use the source alpha - but using the destination alpha lets you do some fun special effects (example).
In XNA you can change the blend function by creating an instance of
The other reason to have an alpha value for the destination buffer is if it is a render target. When you go to use that render target as a texture, the alpha values stored in its buffer will behave as you would expect for a texture.