.NET CLR Introduction with SQL Server 2005
SQL Server 2005 or later version allows hosting DOT NET CLR (Common Language Runtime) Object in Database. That is, you can write Stored Procedure, Triggers or Function with C# or VB and can host those in your SQL Server. Thus CLR extends SQL Server functionality allowing you to write Database Objects in any programming language of your choice.
First of all, CLR is not enabled in SQL Server by default. You have to enable it. This is straight forward using following SQL Commands.
EXEC sp_configure ‘clr enabled’ , ‘1’
Let’s create a SQL Server Project with Visual Studio 2008. Add Database Reference to it.
Add a User Defined Function, give it a name like MyFirstCLRFunction.cs.
public partial class UserDefinedFunctions
public static SqlString MyFirstCLRFunction()
// Put your code here
return new SqlString(“Hello”);
This above code snippet will be generated. For the time being, we are not going to modify this MyFirstCLRFunction() for simplicity. So, this MyFirstCLRFunction() will just return a string “Hello”. Let’s build it and ensure that MyFirstCLRProject.dll file is in Debug folder.
Click on Deploy to deploy MyFirstCLRProject() CLR Function in SQL Server.
Check out if MyFirstCLRProject() Function has been deployed successfully in Database. However, you can do this deployment using SQL Command as well.
CREATE ASSEMBLY MyFirstCLRProject
FROM ‘C:\Documents and Settings\A Rahim Khan.KHANCOMPUTER\My Documents
CREATE FUNCTION MyFirstCLRFunction()
AS EXTERNAL NAME MyFirstCLRProject.UserDefinedFunctions.MyFirstCLRFunction
A Rahim Khan