T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult. Invariably, your project includes a stored procedure or two, requiring some text parsing or complex math operations. Doing this in SQL Server has been difficult. Either you wrote T-SQL code, or you wrote an extended stored procedure/function and used COM to interoperate them. Neither was a good solution, but that is all we had.
In comes SQL Server 2005 with its CLR integration to alleviate these problems. By integrating the CLR, SQL Server 2005 allows you to deploy C# or VB.NET code that is used within the SQL Server process. This means that if you need complex procedural code, you can write it as managed code.
Integrating the CLR into SQL Server is not a step to eliminating T-SQL. As .NET developers, it may seem like a good idea to do all your database code in managed code, but this is not the case. Think of the CLR integration as just another tool in your toolbox. This is the hammer that, I suspect, will be used to hammer in nails, screws, and 2x4s in projects the next year. It will be overused. Dont let your project be the ones caught guilty of this.
Details of the CLR Integration
Integrating the CLR into SQL Server involves a number of different features. Many of those features allow developers to do things they have never had the opportunity to do before in SQL Server. But, before I discuss the features and how they work, its important to consider some details on how the CLR is hosted in SQL Server.
Integrating the CLR into the SQL Server 2005 engine was not done in a trivial way. At the end of the day, SQL Server 2005 has to be a rock-solid implementation of the database. Any new feature has had to endure intense scrutiny for how it will impact the stability of SQL Server.
In the 1.x version of the CLR, the chief client for hosting the CLR was Internet Information Server (IIS). IIS is a peculiar beast. If it finds badly behaving code, it is happy to kill threads and processes and just restart the code. Any code living within IIS was free to allocate memory, threads or even new processes, as it saw fit. Unfortunately, in SQL Server this is the opposite of the needed requirements. If some piece of CLR code starts to act in a bad way, destroying the SQL Server process is the completely wrong thing to do. The health of the SQL Server processes are critical to the stability of the platform.
