CLR integration is probably the most famous new feature of SQL Server 2005. So why did I not talk about this before, maybe in my first blog about those new features? Well, in my opinion the CLR integration is somehow oversold.
I do not mean it is not important to have possibility to extend Transact-SQL with .NET languages. Extending the T-SQL was possible in previous versions as well, with extended procedures. Managed code is much safer than native C or C++ code; therefore, replacing extended procedures with CLR code is very welcome. Still, plain T-SQL code is the safest. T-SQL is a set-oriented language and is not among the fastest languages in row-oriented operations and extensive computations. However – should not we try to use set-oriented code when we are working with relational databases anyway? In addition, should not we perform extensive computations on middle tier? I guess these are two main fears of DBAs when they talk about CLR: possibility that developers will overuse row-oriented operations, and safety threats.
In my opinion, the majority of code in SQL Server is still going to be written in T-SQL. I do not want to underestimate the need for the extensions of T-SQL; however, I doubt we need really many of those extensions. How many additional aggregate, analytical and other functions do we need? If your answer is many, then I would suggest you to check Analysis Services, UDM and MDX language. How many user-defined data types do we need? Well, unless you really need strong constraints built directly into your data type, I would suggest you to check the XML data type, which is definitely a broader standard than your data type.
It seems to me that also the writers of books and articles on CLR integration have problems with good examples. Most of the time you can see examples that use regular expressions. Yes, I like regular expressions and I think they are very useful; and yes, I admit I wrote an example with regular expressions as well. Nevertheless, if regular expressions are so widely useful, should not T-SQL support them like they are supported in SQL dialect of some competitor database systems.
My conclusion is CLR integration is good, but you should use CLR code very carefully. Please do not forget about set-oriented approach and T-SQL solutions. I also wish that Microsoft would not neglect further development of T-SQL in favor of CLR.
