I was just wondering how linked servers handle connections under the hood, primarily how long the connection is open. For example, If I create a linked server to SourceServer on MainServer, and then I execute a SELECT query on MainServer that pulls information from SourceServer, does the connection open at the beginning of execution and terminate on completion? Or is there a connection at all times?
I'm wondering because in languages like C# it's clear: you have commands that explicitly open and close the server connection. In T-SQL, you pull data over a linked server without issuing any 'open' or 'close' statements, so I was wondering if T-SQL is doing this implicitly.
Thank you!