The next version of SQL Server named SQL Server 2005 is completely hyped with the integration of CLR into SQL Server. The introduction of CLR into SQL Server allows developers to write stored procedures, triggers, user defined functions, user defined aggregates and user defined types using .NET languages like VB.NET and C#. This introduction has opened up multiple avenues for developers and we need to be careful in maximizing the feature provided.

Looking at the advantages CLR integration gives, first and foremost is the language and interfaces that CLR languages open up. Now we have richer API's to access and talk when compared to conventional Transact-SQL code. The CLR code is now hosted in the SQL Server database environment. Hence the loading and unloading of assemblies will be in this default Appdomain hosted by SQL Server. This is more clean when compared to the buggy sp_OA* method or extended stored procedures. Microsoft has extended its tool set to give us an unified integration. VS .NET 2005 (earlier codenamed Whidbey) will allow us to seamlessly integrate and create these managed assemblies, UDT's and UDA's. Last but not the least, a much more debated is the compiled code out-performs the Transact code in some areas (string manipulation, regular expression etc).

CLR Integration - Closer look

As described earlier, CLR does open up opportunity for creating managed stored procedures, triggers, UDF's, UDA's and UDT's. The first call to a managed code (term referred to as code run inside the CLR environment) will prompt SQL Server to load the CLR. Later any managed code invocation is run in this boundary. Allocation of memory, using security policy using the CAS, exception handling etc are taken care by this managed environment. A managed code block has access to the BCL (.NET framework Base Class Libraries) that provides complete access to an arsenal of functionality like string manipulation, regular expressions, file access, registry access mechanisms, crypto API's etc.

Interestingly SQL Server 2005 we are addressing a different set of DBA's, DBA's who need to understand the .NET architecture and DBA's who understand conventional T-SQL coding techniques. We need to also track now assemblies are invoked. How security would operate between assemblies and T-SQL code. The fun aspect to this administration would be troubleshooting and performance tuning.

Managed Code Vs Transact SQL Code

This section is very very very important as far as I see. Now the previous versions had a clean line of demarcation while using database code. But with this new feature into picture developers need to take another decision to if a code needs to run in the application tier or in the database tier. I do see a tendency now for developers to write anything and everything in managed code. The last thing any developer would want would be use transact code :).

Fundamentally, transact code is well suited for code that needs high on data access with minimal procedural code. Under such a situation if we were to code in CLR language we would ultimately land up in having chatty calls to the data access layer. To avoid the same SQL Server 2005 has come up with an in-process managed data access mechanism. I am personally not a big fan of the same. Calling data specific code back from an assembly and on an regular fashion will hinder performance for sure. I support CLR languages code strongly for operations that demand high on CPU operations or processing power. Typical implementation of the same would be high string operations or crypto API's.

Note: Even SQL Server 2000 mandated a dedicated machine. Now the introduction of CLR will also mandate more processing power for SQL Server and of course more memory for its function.

Assemblies and Security

This is the next topic that I see it as interesting in the current implementation. All the assemblies are copied into SQL Server and all the assemblies invocation would load and host get hosted by SQL Server. As I mentioned earlier, the CLR will load the assemblies, the assemblies security is scoped under the user context who invoked it. Hence we will have appdomains on a user context and all the assemblies will be loaded. Fundamentally in SQL Server 2005 there are three modes of security access provided for all assemblies. This primarily dictate the action the particular assembly can do. They are:

• Safe
• Extrenal_Access
• Unsafe

All the above permissions are set using the WITH PERMISSION_SET option in the CREATE ASSEMBLY DDL syntax. For all practical purposes the SAFE option would go fine for most of the users. The SAFE option is the default setting. This option would tell that the assembly loaded will do computational activity or can call in-process dataaccess. This also re-interates the fact that the assembly will not move outside the current SQL Server's boundary.

EXTERNAL_ACCESS on the contrary opens up the next set of functionality wherein the assembly needs to access resources outside the boundary of SQL Server context. This will include accessing of registry, file system, network etc. For the same to work correctly the creator of the assembly needs to have appropriate permissions to access the external resource.

UNSAFE as it suggests, is calls to API's that are potentially unsafe like the Win32 API's or unsafe datatypes (can be possible in C# language). These assemblies can call unmanaged code access calls too.

Conclusion

CLR does open up loads of opportunity for bad coding practices. In this article we have just taken a sneak preview to how we can use CLR functionality in SQL Server 2005. My request to all developers is to weigh the feature and look at the functional requirement before jumping into using the CLR langauages directly. SQL Server 2005 is still in the Beta program, hence not much performance results can be published. Evaluate, test and test before taking a decision. The power is yours ...