A Tale of Two CLRs

It's no secret that one of the most important features of SQL Server 2005 is the integration of the Common Language Runtime (CLR) with the database. However, IBM's recent release of DB2 UDB 8.2, codenamed Stinger, surprised a lot of people and stole SQL Server 2005's thunder by supporting database-object creation through .NET. Although IBM got CLR support out the door first, don't make the mistake of thinking that the CLR support DB2 UDB 8.2 offers is the same as the CLR support SQL Server 2005 will provide. There are some profound differences in the way each product implements support for the .NET CLR.

First, the two products support different types of database objects. DB2 UDB 8.2 supports the creation of only stored procedures and functions and integrates the creation only of stored procedures (not other object types) into the Visual Studio 2003 development environment. DB2 UDB 8.2 currently doesn't provide integration with Visual Studio 2005, although Visual Studio 2005 is still in beta, so this could change. In contrast, SQL Server 2005 supports the creation through .NET of a full range of database objects: stored procedures, functions, triggers, and so on. Visual Studio 2005 includes templates for all of these objects. It's worth noting that you don't absolutely need Visual Studio to use the .NET features of either product. Their respective Visual Studio integration pieces enrich the development process, but you can also develop .NET database objects for either platform just by using the .NET Framework Software Development Kit (SDK).

Additionally, DB2 UDB and SQL Server 2005 interact with the CLR in very different ways. With DB2 UDB 8.2, the CLR is an external implementation, and the database executes calls to the CLR exactly as an application might use the .NET Framework. In this scenario, the .NET Framework manages its own resources independently of the database. SQL Server 2005, however, hosts the CLR in-process, giving much tighter integration. SQL Server 2005's in-process hosting yields several important advantages. First, it lets SQL Server control CLR execution, putting essential functions such as memory management, garbage collection, and threading under the control of the SQL Server database engine rather than letting the CLR manage them independently. The database engine knows more about the system requirements and can manage memory and threads better than the CLR can. In-process hosting will provide better performance and scalability.

The two implementations also offer big differences in the development experience. DB2 UDB supports creation and deployment of assemblies, but it stops there. SQL Server 2005's integration with Visual Studio 2005 supports seamless debugging of those objects after their creation and deployment. Visual Studio 2005 automatically generates a T-SQL wrapper that you can use for testing .NET database objects and fully supports debugging of .NET database objects with all the capabilities you'd expect to have in client-side code. With SQL Server 2005, you can set breakpoints and seamlessly step between .NET code and T-SQL code.

Although both DB2 UDB 8.2 and SQL Server support the creation of .NET database objects, the two platforms are integrated with the .NET Framework and the Visual Studio development environment in profoundly different ways. IBM's DB2 UDB 8.2 may have gotten .NET integration first, but Microsoft and SQL Server 2005 got it right.