c# - What is the SQL Server CLR Integration Life Cycle?

Question

How are CLR (.NET) objects managed in SQL Server?

The entry point to any CLR code from SQL Server is a static method. Typically you'll only create objects that exist within the scope of that method. However, you could conceivably store references to objects in static members, letting them escape the method call scope. If SQL Server retains these objects in memory across multiple stored procedure/function calls, then they could be useful for caching applications -- although they'd be more dangerous too.

How does SQL Server treat this? Does it even allow (non-method) static members? If so, how long does it retain them in memory? Does it garbage collect everything after every CLR call? How does it handle concurrency?

Answer 1

In "Pro SQL Server 2005 Assemblies" by Robin Dewson and Julian Skinner, it says that "Assemblies loaded into a database, like other database objects, are owned by a database user. All assemblies owned by the same user in the same database will run within the same AppDomain. Assemblies owned by a different user will run within a separate AppDomain."

What this tells me is that if you're working with a single database and all the assemblies you load in with the CREATE ASSEMBLY statement have the same owner, then your assemblies will all run in the same app domain. However, being in the same AppDomain does not mean using the same code-base, so even the same dll can be loaded into the same application domains multiple times, and it's types will not match up, even though they have the same name. When same-named types are from different code-bases, their static variables will be different instances as well.

The only way I can see to use static variables safely in SQL Server CLR environment with multiple assemblies is to actually just use a single assembly. You can use the ILMerge utility with the "UnionMerge" option to pack all your assemblies into one and merge classes with the same name. This should guarantee that for a given database, in your sole assembly, your static variables will work just like they would in a stand-alone application. I think it's safe to assume the application domain isn't unloaded and reloaded at every request, but you can't depend on it never being unloaded, since that will happen whenever there is an unhandled error (at least if it's running in unsafe mode).