SQL SERVER – 2005 – What is CLR?

CLR is Common Language Runtime.

Here is the diagram which explains the architecture of the it.

SQL SERVER - 2005 - What is CLR? sqlclr-800x600

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 this 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 the this stored procedure are compiled rather than interpreted like T-SQL, they can provide a significant performance advantage for code that’s executed multiple times. I think anybody who wants to start using CLR must start with this simple example. Overuse of the CLR is also not advisable and it should be avoided. Blog article ends with proper conclusion.

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

Let me know your thought about this blog post. Do you use this kind of stored procedure on your production server? If yes, what is the purpose for using it.

Reference: Pinal Dave (https://blog.sqlauthority.com), Image source: Wikipedia

CLR, SQL Function, SQL Server, SQL Utility
Previous Post
SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use – Part 2
Next Post
SQL SERVER – 2005 – Retrieve Processes Using Specified Database

Related Posts

4 Comments. Leave new

  • Nicholas Paldino [.NET/C# MVP]
    March 18, 2008 10:21 pm

    Curious, what is the rationale behind the statement:

    Overuse of CLR is also not advisable and it should be avoided.

    I ask because overuse of the CLR is not necessarily a bad thing, but rather, it depends on how the CLR is overused in SQL Server.

  • Islam Khalil
    June 2, 2008 5:25 pm

    your blog was very helpfull for me hope to accpet my thanx

  • sir will u tell me somthing spical us for trigger & join’s.

  • sir thanks for u,
    according to me your blog was very helpfull every IT student.


Leave a ReplyCancel reply

Exit mobile version