\nSo, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup.\n<\/p>\n
\nI have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE\/TIME-related data type series are not .NET-based, but you can use them in SQLCLR procs, as you can use NVARCHAR data type. There are some restrictions on DATE\/TIME series, more about that later. My second user assembly is named hiertest. It uses the HierarchyID data type in SQLCLR code. That's (of course) OK too. Both my user assemblies are owned by DBO. Both catalog as SAFE_ACCESS. There are no user assemblies in pubs database. Turn on SQL Profiler.\n<\/p>\n
\nIn pubs: SELECT * FROM sys.assemblies;\n<\/p>\n
\nIn profiler:
\nAssembly Load event:
\nAssembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
\nIn SQL Server Log:
\n Common language runtime (CLR) functionality initialized using…
\n AppDomain 2 (32767.sys[runtime].1) created
\n
\nStill in pubs: declare @h hierarchyid; select HierarchyID::GetRoot(); — invoke static method of hierarchyid data type\n<\/p>\n
\nIn profiler:
\n Assembly Load Succeeded for Microsoft.SqlTypes.Types
\nIn SQL Server Log:
\n Unsafe assembly 'microsoft.sqlserver.types…" loaded into AppDomain 2 (32767.sys[runtime].1)\n<\/p>\n
\nUSE test
\nGO\n<\/p>\n
\nIn test: SELECT * FROM sys.assemblies;
\nIn SQL Server log:
\nAssembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
\n
\nIn test: EXEC dbo.somedateproc — my SQLCLR proc that uses date.
\nIn SQL Server log:
\n AppDomain 3 (test.dbo[runtime].2) created
\nIn profiler:
\n Assembly Load Succeeded for datetimetest
\n
\nIn test: SELECT * FROM dbo.AncestorAndSelf('\/'); — my SQLCLR UDF that uses HierarchyID
\nIn SQL Server log:
\n Unsafe assembly 'microsoft.sqlserver.types…" loaded into AppDomain 3 (test.dbo[runtime].2)
\nIn profiler:
\n Assembly Load Succeeded for Microsoft.SqlTypes.Types
\n Assembly Load Succeeded for hiertest\n<\/p>\n
\nSo what happened??\n<\/p>\n
\nThere is only one resource database appdomain, it gets created when system functions (sys.assemblies) use system assemblies. The actual assembly that contains the system .NET data types loads only when it's required, into the resource database's appdomain. By the way, the declaration of a (NULL) hierarchyID variable isn't enough to load the assembly, you must actually use the variable. If you use only TSQL and the new data types, only this one appdomain is needed, regardless of how many different databases use them.\n<\/p>\n
\nAdditonal appdomains are created on a per-database, per-assembly owner basis as in SQL Server 2005. Each "user" appdomain will also load the system assembly Microsoft.SqlTypes.Types, if and only if it needs it. That is, if you use HierarchyID (or Geometry\/Geography) in a SQLCLR procedure.\n<\/p>\n
\nOne last bit. Why are the appdomains referred to as: "AppDomain 2 (32767.sys[runtime].1)" and what happened to AppDomain 1? When .NET is loading into any executing process, there is a single appdomain created, the default appdomain. AppDomain 1. SQL Server doesn't load Microsoft.SqlServer.Types into this default appdomain, but starts another for the resource database. That's AppDomain 2 (into the process). The ".1" in "32767.sys[runtime].1" is first user appdomain.\n<\/p>\n
\nDatabase administrators like to know about everything going on in "their" database, and with good reason…if the database fails (or even runs slowly) its (s)he who gets the first phone call for help. SQLCLR is still relatively new. Hope this was helpful in explaining what's going on. But remember, the exact appdomain implementation could be refined further in later releases. Cheers.<\/p>\n","protected":false},"excerpt":{"rendered":"
So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup. I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE\/TIME-related data […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,38],"tags":[],"class_list":["post-766","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-sqlclr"],"yoast_head":"\n