Create CLR Functions
APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.
Creating a CLR function in SQL Server involves the following steps:
Define the function as a static method of a class in a language supported by the .NET Framework. For more information about how to program functions in the common language runtime, see CLR User-Defined Functions. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).
Create the function that references the registered assembly by using the CREATE FUNCTION statement.
Deploying a SQL Server Project in MicrosoftVisual Studio registers an assembly in the database that was specified for the project. Deploying the project also creates CLR functions in the database for all methods annotated with the SqlFunction attribute. For more information, see Deploying CLR Database Objects.
The ability of SQL Server to execute CLR code is off by default. You can create, alter, and drop database objects that reference managed code modules, but these references will not execute in SQL Server unless the clr enabled Option is enabled by using sp_configure (Transact-SQL).
Accessing External Resources
CLR functions can be used to access external resources such as files, network resources, Web Services, other databases (including remote instances of SQL Server). This can be achieved by using various classes in the .NET Framework, such as
System.Sql, and so on. The assembly that contains such functions should at least be configured with the EXTERNAL_ACCESS permission set for this purpose. For more information, see CREATE ASSEMBLY (Transact-SQL). The SQL Client Managed Provider can be used to access remote instances of SQL Server. However, loopback connections to the originating server are not supported in CLR functions.
To create, modify, or drop assemblies in SQL Server
To create a CLR function
Accessing Native Code
CLR functions can be used to access native (unmanaged) code, such as code written in C or C++, via the use of PInvoke from managed code (see Calling Native Functions from Managed Code for details). This can allow you to re-use legacy code as CLR UDFs, or write performance-critical UDFs in native code. This requires using an UNSAFE assembly. See CLR Integration Code Access Security for cautions about use of UNSAFE assemblies.
Create User-defined Functions (Database Engine)
Create User-defined Aggregates
Execute User-defined Functions
View User-defined Functions
Common Language Runtime (CLR) Integration Programming Concepts