Building Database Objects with Common Language Runtime (CLR) Integration

You can build database objects using the SQL Server integration with the .NET Framework common language runtime (CLR). Managed code that runs inside of Microsoft SQL Server is referred to as 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 are mapped to public, static (shared in Microsoft 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 Transact-SQL declaration and can be used anywhere in SQL Server that the Transact-SQL 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.

Note

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 is beneficial. However, if a user-defined function accesses data, execution will be on a serial plan. When executed on a server version prior to SQL Server 2008, if a user-defined function contains LOB parameters or return values, execution also must be on a serial plan. For more information, see Parallel Query Processing.

Note that Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server includes the .NET Framework pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

The following table lists the topics covered in this section.