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 that Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server 2005 includes the .NET Framework 2.0 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.

Topic Description

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 Stored Procedures

Describes how to implement and use CLR stored procedures.

CLR User-Defined Types

Describes how to implement and use CLR user-defined types.

CLR Triggers

Describes how to implement and use CLR triggers.