Skip to content

Latest commit

 

History

History
57 lines (38 loc) · 5.15 KB

create-clr-functions.md

File metadata and controls

57 lines (38 loc) · 5.15 KB
title description author ms.author ms.date ms.service ms.topic helpviewer_keywords
Create CLR Functions
Learn how to create a database object inside SQL Server that is programmed in the .NET Framework common language runtime (CLR).
rwestMSFT
randolphwest
12/27/2024
sql
conceptual
CLR functions [SQL Server]
user-defined functions [SQL Server], CLR

Create CLR functions

[!INCLUDE sqlserver2016]

You can create a database object inside an instance of [!INCLUDE ssNoVersion] that is programmed in an assembly created in the [!INCLUDE dnprdnshort] common language runtime (CLR). Database objects that can use the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

Creating a CLR function in [!INCLUDE ssNoVersion] involves the following steps:

  • Define the function as a static method of a class in a language supported by the [!INCLUDE dnprdnshort]. 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 [!INCLUDE dnprdnshort] by using the appropriate language compiler.

  • Register the assembly in [!INCLUDE ssNoVersion] by using the CREATE ASSEMBLY statement. For more information about assemblies in [!INCLUDE ssNoVersion], see Assemblies (Database Engine).

  • Create the function that references the registered assembly by using the CREATE FUNCTION statement.

Executing CLR code is off by default in [!INCLUDE ssNoVersion]. You can create, alter, and drop database objects that reference managed code modules, but these references don't execute in [!INCLUDE ssNoVersion], unless the clr enabled server configuration option is enabled by using sp_configure.

Deploying a SQL Server Project in [!INCLUDE vsprvs] 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 Deploy CLR database objects.

Access external resources

You can use CLR functions to access external resources such as files, network resources, web services, and other databases (including remote instances of [!INCLUDE ssNoVersion]). CLR functions can use various classes in [!INCLUDE dnprdnshort], such as System.IO, System.WebServices, 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.

The SQL Client Managed Provider can be used to access remote instances of [!INCLUDE ssNoVersion]. However, loopback connections to the originating server aren't supported in CLR functions.

Create, modify, or drop assemblies in SQL Server

Create a CLR function

Access native code

CLR functions can 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). You can reuse legacy code as CLR UDFs, or write performance-critical UDFs in native code, and requires using an UNSAFE assembly. See CLR integration Code Access Security for cautions about use of UNSAFE assemblies.

Related content