Will T-Sql SET parameters be reset with pooled .NET connections?

Question

The only SET parameter that I have found specific guidance for is SET TRANSACTION ISOLATION LEVEL:

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

What about other SET parameters, like SET XACT_ABORT or SET DEADLOCK_PRIORITY? If you set those during a stored procedure (or a batch submitted from C#), will they go back to their previous values the next time the connection gets used?

Answer 1

I believe Transaction Isolation Level remains set for the rest of the current session. That means that even after such a stored proc returns, if client cide calls another stored proc on the same connection (in the same session) that second proc will be executed under the same isolation level.

Answer 2

By default (that is, unless you include "Connection Reset=false" in the connection string), .NET resets the connection with the sp_reset_connection stored procedure before reusing it; among other things it undoes any SET commands that have been issued since the connection was opened.

See this article for more details.