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.

• Getting Started with CLR Integration
  Provides a brief overview of the libraries and namespaces required to compile object using CLR integration with SQL Server. 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 SQL Server data types and their .NET Framework equivalents.

• Overview of CLR Integration Custom Attributes
  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.