What is the syntax for creating a new assembly in SQL Server 2005?

Assemblies can be created using CREATE ASSEMBLY command. It takes the path of the DLL as the parameter.

Example:

CREATE DIRECTORY Sample

FROM ‘\\sampleserver\CodeLib\Mysample.dll’

When the CREATE ASSEMBLY is executed, the DLL is copied to master database.

CLR Integration vs. Extended Stored Procedures

CLR Integration provides a more convenient and robust solution to Extended stored procedures for implementing server side logic. Using Common Language Runtime integration allows the results

RECONFIGURE WITH OVERRIDE

Here, 1 is the configuration setting.

How to create functions in SQL Server using .NET?

Functions in SQL server can be created using the .NET common language interface or CLR. The functions code is written and then complied into a .NET assembly to deploy on the SQL server. This can be achieved either by using a user friendly interface of Visual studio 2005 or compiling the visual studio class library into an assembly.

Syntax:

CREATE FUNCTION MyFunction()
RETURNS INT AS
EXTERNAL NAME
MyAssembly:[MyClass]::MyFunction
Here, EXTERNAL NAME clause is to link the user-defined function name to the appropriate method in the .NET assembly.