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://darkslategrey-bat-805937.hostingersite.com), Image source: Wikipedia
