CLR is Common Language Runtime.
Here is the diagram which explains the architecture of the it.
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
4 Comments. Leave new
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.
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.