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.