After we have registered a DLL in SQL Server (in Programmability -> Assemblies), can we call any type of method from that .NET assembly in SQL Server? Or just static ones?
SQL Server – Methods Callable from a .NET Assembly
sql serversql-clr
Related Question
- SQL Server 2014 – Assembly ‘system.web’ Not Found in SQL Catalog
- Sql-server – CLR Assemblies and SQL Server Availability Groups
- Sql-server – SQL Server installation errors assembly
- Sql-server – .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)
- SQL Server 2017 – Error Msg 10314, Level 16, State 11 with SAFE Assembly
- Sql-server – extend SQL Server with C++ or C
- Adding .Net Assembly to SQL CLR Without TRUSTWORTHY ON
- SQL Server Assembly – SQL Server Assembly on Failover Cluster After Framework Update
Best Answer
SQLCLR is a very limited CLR host. Yes, you can only call static methods due to the App Domain being shared across all sessions (i.e. @@SPID), and hence any "shared" instances or variables would be very much not "thread safe". App Domains are created on a per-Database, per-"owner" (i.e. whatever User is listed in the
AUTHORIZATION
clause ofCREATE ASSEMBLY
, ordbo
as the default if that clause is omitted), so there is a minimal amount of separation, but for any given method that is exposed via aCREATE {object} AS EXTERNAL NAME
statement, any User in any Session executing that SQLCLR Stored Procedure, Function, Trigger, or User-Defined Type will be hitting the exact same App Domain. This is why you are also not allowed to store values in static class variables (unless they are marked asreadonly
) as they are shared across Sessions and one Session can overwrite the value that another Session just stored and hence you would get odd / erratic / unpredictable behavior.Regarding SQLCLR being a restricted environment:
Please see the following MSDN page for a list of what is not allowed in SQLCLR: CLR Integration Programming Model Restrictions.
Please see the following MSDN page for a list of what few .NET Framework libraries are included in SQLCLR: Supported .NET Framework Libraries.
For more information on working with SQLCLR in general, please see the series I am writing on this topic on SQL Server Central (free registration is required to read their content, but it's worth it): Stairway to SQLCLR.