SQLCLR state persistence when calling stored procedures in a batch

Static variables do retain their value between executions of any methods within the Assembly (this, of course, requires that the Assembly be marked as UNSAFE). However, this has nothing to do with the Query Optimizer recognizing anything since the Query Optimizer has no insight into what is inside of the Assembly. Static variables retain their values due to the App Domain persisting until: service restart, forced unload due to memory pressure, security change made to the database containing the App Domain, security change to an Assembly in the App Domain, etc. The fact that static variables share their values between SQLCLR objects (not just stored procedures) is due to SQLCLR sharing the App Domain across all sessions.

Please note that while static variables can share values between executions, that:

• This has nothing to do with batches. The values can be shared across query batches even.

• It is not guaranteed that any value will always persist between all executions due to SQL Server being allowed to unload the App Domain even while a SQLCLR object is currently executing. So if two SQLCLR objects — Stored Procedures, perhaps — were executed one after the other, even in the same batch, it is possible for the App Domain to be unloaded ^** prior to the execution of the 2nd Stored Procedure (i.e. between queries), and hence the App Domain would be started again to process the 2nd Stored Procedure, but with no indication of any prior calls, hence no prior state in any static variables.

  Meaning, if the value in the static variable being cleared will cause a subsequent execution of a SQLCLR object to do something unexpected, then do not use static variables for that purpose. They are fine for caching, but can be unreliable for many uses. IN FACT, there is another question, here on S.O., where someone was doing exactly this – caching some values to improve performance between executions of UPDATE statements – and ran into this problem. In that linked question, the O.P. has a main SQLCLR stored procedure that stores values in a static variable and executes other SQLCLR stored procedures that read from that static variable. The main SQLCLR stored procedure runs until its sub-calls complete successfully, but during its execution, the App Domain is marked for unload. At that point, all new SQLCLR executions happen in a new App Domain, even though the App Domain with the main SQLCLR stored procedure is still running. But, the App Domain for the main SQLCLR stored procedure has a state of "DOOMED" and cannot be accessed by any new SQLCLR calls (since any new calls happen in the new App Domain).

IF you need to persist state between executions within the same Session that is NOT shared with other Sessions, you can:

• Use SET CONTEXT_INFO
• Use sp_set_session_context (starting in SQL Server 2016)
• Create and populate a local temporary table

Each of those needs to be created / set in a SQLCLR stored procedure using "Context Connection = true;" as the Connection String. But they should be readable in a Scalar UDF, also using "Context Connection = true;" as the Connection String.

^** An App Domain being marked for unload due to memory pressure, or due to a security change to the Database or Assembly, will not kill a currently running SQLCLR process. The App Domain remains running but is unloaded immediately upon the process completing.

HOWEVER, if the App Domain is unloaded due to .NET Integration / "clr enabled" being turned off via sp_configure, then the SQLCLR process will be immediately killed.