SQL Server 2005 CLR Integration and the DBA
This is a very hot and debatable topic. Something that you can spend your entire evening on :-) During my TechEd 2005 visits across India and giving sessions on SQL Server 2005, many a times, the question that people had was “why integrate the CLR inside of SQL Server 2005” and “what should I now know as a DBA”? Valid questions and will require some sort of an answer.
To answer the first question as to why the CLR integration was done, well, the core reason which I think was well deserved was to provide a type-safe and secure model for developing “extensions” to SQL Server. In the past, you could extend SQL Server only through extended stored procedures and these were filled with all sorts of issues. With the CLR integration now done, you can write .NET code for extending SQL Server and have the entire power of the .NET Framework BCL at your disposal (well, not all, but many of them). Typical examples that I have seen are an email address validation routine written as a .NET code callable from the SQL Server. Implementing this sort of a check in T-SQL can take many lines of code and maintenance headache, whereas in the .NET world, you can use Regular Expressions and finish the same in just a couple of lines! That apart, there are truly some scenarios where T-SQL is just not suited for the job and vice-versa (where .NET is not suited inside SQL Server). This is the decision that DBAs will have to take as they interact with developers in their organizations and formulate the standards of coding etc.
Greg Low, a SQL Server MVP from Australia, has a good webcast that addresses this problem. In this webcast, he gives an introduction to the .NET Framework and the basic elements that a DBA will need to know and then proceeds to show some examples of typical code. The interesting part of the webcast is the set of “best practices” of sorts that DBAs will have to adopt to deal with this new extension to SQL Server and this is good to know. You can view the presentation here: A SQL Server DBA's Guide to the .NET Framework and CLR Integration.