Any code that runs under the Common Language Runtime (CLR) hood is managed code. The CLR is the core of the .NET environment providing all necessary services for the execution of the managed code. SQL Server 2005 has a tight integration with the CLR which enables the developer to create stored procedures, triggers, user defined functions, aggregates and user defined types using the managed code.

T-SQL suits best when you need to perform little procedural logic and access data from the server. If your data needs to undergo complex logic then the better option is using managed code. When working on data intensive operations, working in T-SQL would be an easy approach, but T-SQL lacks the ease of programming. You might end up in lot of lines of coding in trying to simulate operations that are specific to character, string operations, arrays, collections, bit shifting and so forth. While working with mathematical operations and regular expressions, you might need a language that provides an easy, clean yet powerful way of handling such operations. If you encounter situation where you need to perform such operations using T-SQL, then it is really going to be annoying.

Integrating DML operations with managed code also helps bifurcate logic into classes and namespaces, which is somewhat similar to what we have schemas in the database. Now saying this, it should be understood that integrating CLR into SQL Server does not replace the business tier of your application. The benefits of integrating the CLR with SQL Server include:

1. The T-SQL statements that you execute actually run on the server end. But at times when you want to distribute the load between the client and server, you could go with the managed code. So using managed code you could perform critical logic operations in client side so that the server could be busy only with data intensive operations.

2. The fact that SQL Server provides you extended stored procedures to avail certain system related functions from your T-SQL code, but at the same time you may have to compromise with the integrity of the server. When it comes to managed code, it provides type safety, effective memory management and better synchronization of services which is integrated tightly with the CLR and, hence, the SQL Server 2005. So this means that integrating CLR with SQL Server provides a scalable and safer means for accomplishing tasks which are tougher or almost impossible using T-SQL.

3. .NET Framework provides a rich support for handling XML based operations from managed code; although realizing the fact the SQL Server supports XML based operations, you could perform such operations using .NET with little effort when compared to using T-SQL scripts.

4. Nested transactions in T-SQL have limitations when dealing with look back connections, whereas this could be better achieved using managed code by setting the attribute "enlist=false" in the connection string.

5. When working T-SQL you may not be able to fetch rows which form the middle of the operation from a result set until the execution gets finished. This is termed as pipelining of results which could be achieved with CLR integration.

If you could check your database configuration you could notice that the CLR Integration is turned off by default. Enabling or disabling of CLR integration could be done by setting the "clr enabled" option to 1 or 0. Once the CLE integration is disabled, all the executing CLR procedures are unloaded across all application domains. To turn it on you need to use the following.

Listing 1

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

The RECONFIGURE statement ensures that you need not restart the server with the change in the configuration. But if one among the several configuration options fails, none of the configured values would take effect.

But to configure the above you require ALTER SETTINGS server level permission to enable it. Again, for achieving that you need to be a member of serveradmin and sysadmin roles.

Using RECONFIGURE ensures that the update in the configuration does not need server restart. The configured values do not take effect in case you reconfigure several options in one time and one of the option fail.