SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

5) Add new stored procedure template from following screen.

6) Once template added it will look like following image.

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
public static void CLRSPTest()
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.

USE AdventureWorks

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – 2005 – Introduction to CLR and Integration to CLR with SQL – Simple Sample Example of CLR

It is always interesting to learn new technology. I always wanted to learn about CLR but I somehow never got chance to practice on it. I had requested excellent blog writer and .net expert Chirag Darji to write introduction to CLR and integration with SQL Server. I think he has posted some great stuff on this blog about introduction to CLR integration to SQL Server.

Chiarg starts with one question which comes to our mind when we hear CLR; What is CLR and why do we need it?

CLR is Common Language Runtime.

As we all know SQL server has performance issue with cursor and looping. When we use recursive functions or a cursor in stored procedures we have to compromise with performance. While integrating CLR we can write the looping and recursive functionalities in C# (or any other language). CLR stored procedures can take advantage of the built-in functionality provided by the classes in the .NET Framework, making it relatively easy to add functionality such as complex mathematical expressions or data encryption. Plus, since CLR stored procedure are compiled rather than interpreted like T-SQL, they can provide a significant performance advantage for code that’s executed multiple times.

Chirag continues to discuss about how to create stored procedure in CLR and their integration with SQL Server. Blog articles proceeds with simple example and lots of diagrams explaining integration. I think anybody who wants to start using CLR must start with this simple example. Overuse of CLR is also not advisable and it should be avoided. Blog article ends with proper conclusion.

CLR stored procedures can not be used as replacement of T-SQL statements. Execution of normal T-SQL statement is much faster than CLR Stored Procedure. We can use CLR Stored Procedure to avoid cursors and complex computation.

Good Work Chirag and keep on posting articles like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Chiarg Darji

SQL SERVER – Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support


Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support


1) Enable Server for CLR Support.

2) Run following query in Query Analyzer

sp_CONFIGURE 'clr_enabled',1

If CLR procedure is used without enabling CLR, it will show error message as .NET Framework is not enabled.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Enable CLR using T-SQL script

Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default.
This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR.

EXEC sp_CONFIGURE 'show advanced options' , '1';
EXEC sp_CONFIGURE 'clr enabled' , '1'

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL