CREATE ASSEMBLY (Transact-SQL) Creates a managed application module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database. Transact-SQL Syntax Conventions assembly_name Is the name of the assembly. The name must be unique within the database and a valid identifier. AUTHORIZATION owner_name Specifies the name of a user or role as owner of the assembly. owner_name must either be the name of a role of which the current user is a member, or the current user must have IMPERSONATE permission on owner_name. Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest file name that corresponds to the assembly.
<client_assembly_specifier> cannot be specified if the logged in user is being impersonated. varbinary_literal Is a varbinary literal. varbinary_expression. CLR User-Defined Functions. User-defined functions are routines that can take parameters, perform calculations or other actions, and return a result. Beginning with SQL Server 2005, you can write user-defined functions in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#. There are two types of functions: scalar, which returns a single value, and table-valued, which returns a set of rows. The following table lists the topics in this section. CLR Scalar-Valued Functions Covers implementation requirements and examples of scalar-valued functions.
CLR Table-Valued Functions Discusses how to implement and use table-valued functions (TVFs), as well as differences between Transact-SQL and common language runtime (CLR) TVFs. CLR User-Defined Aggregates Describes how to implement and use user-defined aggregates.
Generate create assembly from <binary bits> script. CLR Scalar-Valued Functions. A scalar-valued function (SVF) returns a single value, such as a string, integer, or bit value.You can create scalar-valued user-defined functions in managed code using any .NET Framework programming language. These functions are accessible to Transact-SQL or other managed code. For information about the advantages of CLR integration and choosing between managed code and Transact-SQL, see Overview of CLR Integration. .NET Framework SVFs are implemented as methods on a class in a .NET Framework assembly.
The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, except varchar, char, rowversion, text, ntext, image, timestamp, table, or cursor. When implementing a .NET Framework SVF in a .NET Framework language, the SqlFunction custom attribute can be specified to include additional information about the function. Scalar-valued user-defined functions may be deterministic or non-deterministic. Table-Valued Parameters [Visual Basic] Creating CLR Functions. You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR).
Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types. Creating a CLR function in SQL Server involves the following steps: Define the function as a static method of a class in a language supported by the .NET Framework. 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 .NET Framework by using the appropriate language compiler.
Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine). To create, modify, or drop assemblies in SQL Server To create a CLR function.