Skip to content

Latest commit

 

History

History
51 lines (41 loc) · 4.37 KB

building-database-objects-with-common-language-runtime-clr-integration.md

File metadata and controls

51 lines (41 loc) · 4.37 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Use Common Language Runtime (CLR) Integration to Build Database Objects
Build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR).
rwestMSFT
randolphwest
12/27/2024
sql
clr
reference
routines [CLR integration]
database objects [CLR integration], building
common language runtime [SQL Server], building database objects
managed code [SQL Server], database objects
building database objects [CLR integration]
.NET Framework routines [SQL Server]

Build database objects with common language runtime (CLR) integration

[!INCLUDE SQL Server]

You can build database objects using the [!INCLUDE ssNoVersion] integration with the [!INCLUDE dnprdnshort-md] common language runtime (CLR). Managed code that runs inside of [!INCLUDE ssNoVersion] is called a CLR routine. These routines include:

  • Scalar-valued user-defined functions (scalar UDFs)
  • Table-valued user-defined functions (TVFs)
  • User-defined procedures (UDPs)
  • User-defined triggers

CLR routines have the same structure in managed code. They're mapped to public, static (shared in Visual Basic .NET) methods of a class. In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework. UDTs and user-defined aggregates are mapped to entire .NET Framework classes.

Each type of .NET Framework routine has a [!INCLUDE tsql] declaration and can be used anywhere in [!INCLUDE ssNoVersion] that the [!INCLUDE tsql] equivalent can be used. For instance, scalar UDFs can be used in any scalar expression. A TVF can be used in any FROM clause. A procedure can be invoked in an EXEC statement or invoked from a client application.

Execution of a CLR object (user-defined function, user-defined type, or trigger) on the common language runtime can take place on multiple threads (parallel plan), if the query optimizer decides it's beneficial. However, if a user-defined function accesses data, execution is on a serial plan.

The following table lists the articles covered in this section.

Article Description
Get started with CLR integration Provides a brief overview of the libraries and namespaces required to compile object using CLR integration with [!INCLUDE ssNoVersion]. Includes an example "Hello World" CLR stored procedure.
Supported .NET Framework libraries Provides information on the .NET Framework libraries supported by CLR integration.
CLR integration programming model restrictions Provides information about CLR integration programming model restrictions.
SQL Server data types in the .NET Framework An overview of [!INCLUDE ssNoVersion] data types and their .NET Framework equivalents.
CLR integration: custom attributes for CLR routines Provides information about CLR integration custom attributes.
CLR user-defined functions Describes how to implement and use the various types of CLR functions: table-valued, scalar, and user-defined aggregate functions.
CLR user-defined types Describes how to implement and use CLR user-defined types.
CLR stored procedures Describes how to implement and use CLR stored procedures.
CLR triggers Describes how to implement and use CLR triggers.

Related content