CLR stands for Common Language Runtime and is a technology developed by Microsoft that allows you to use managed .NET code in SQL Server environment. In other words, you can write your stored procedures, triggers and user-defined functions, aggregates and types in a .NET language. In order to do so, you need to create a class library project that contains them, import your assembly to your SQL server and tie it all together.
The technology is available starting with Microsoft SQL Server 2005. Here is your first steps:
First, verify that CLR is enabled on the system:
SELECT * FROM sys.configurations WHERE name = "clr enabled"
sys.configurations table is a system table that contains your global SQL Server configuration properties. This statement will return either 0 or 1, indicating whether CLR is enabled. If CLR is disabled, you can enable it by running the following statement:
sp_configure 'show_advanced_options', '1' GO RECONFIGURE; GO sp_configure 'clr enabled', '1'; GO RECONFIGURE; GO
sp_configure stored procedure configures system options. Because CLR is an advanced option, we need to enable advanced options first. The
RECONFIGURE statement is needed to actually update the value – think of it as a
COMMIT on your configuration changes. Also note that it may not always update the configuration right away, according to Microsoft. So now, all that’s left is to change the
clr enabled value to 1 to enable it and we are done!
Your next step is to Create a CLR assembly and a .NET SQL function (C#)!