Choosing Between the CLR and T-SQL in SQL Server 2005 : Page 3

The CLR integration features in SQL Server 2005 enable managed code to run inside the database, but developers now need to choose between CLR-based features and T-SQL when building applications. Find out when each is appropriate.

by Randy Holloway

Jun 14, 2005

Page 3 of 3

Leveraging .NET Languages in SQL Server
Another factor to consider when choosing between T-SQL and managed code is that, with managed code, you can select where you want your code to executeon the server or the client. Both T-SQL and managed code can be run on the server, placing code and data close together, and letting you take advantage of the processing power of the server. But with the rapid expansion of computing power, many researchers now advocate placing computation as close to the data as possible. On the other hand, you may wish to avoid placing processor-intensive tasks on your database server. Because most client computers today are powerful, you may want to take advantage of this processing power by placing as much code as possible on the client. The architecture of your application will play a big factor here, as will the number and nature of your clients accessing the server. Managed code can run on a client computer, while T-SQL cannot. Using managed code will provide you with more flexibility as a result.
Extended Stored Procedures vs. CLRSQL
Using CLRSQL is not the only way to provide extended functionality to SQL Server. Developers have been able to build extended stored procedures in unmanaged languages that perform functionality not possible with T-SQL stored procedures for quite some time; however, extended stored procedures can compromise the integrity of the SQL Server process itself, while managed code verified to be type-safe will not compromise that integrity.

Additionally, memory management, scheduling of threads and fibers, and synchronization services are deeply integrated between the managed code of the CLR and SQL Server. These resource management features provide a wealth of reliability compared to that previously available with extended stored procedures, which have direct access to memory and a require wide range of server resources. CLR integration in SQL Server provides a more secure, reliable, and scalable method to write procedures compared to extended stored procedures.
Mixed Emotions
Some people have expressed mixed emotions with respect to this new functionality, concerned that the benefits may be outweighed by the confusion to developers and a lack of conclusive support for the architectural benefits that are often cited. In addition, there are concerns about the performance of the CLR in SQL Server. While the CLR has been optimized to be hosted efficiently within SQL Server and can be configured as to whether or not it can even run inside the server process, until it is applied in the real world such concerns will remain. However, Microsoft is making it clear that best practice will be to write the stored procedures you need to perform tasks not possible in T-SQL using .NET code. With both tools available, the key for developers will be to pick the right tool for the job at hand.

Together, the.NET framework and the Common Language Runtime (CLR) form Microsoft's preferred platform for executing applications. This platform is one of the core technologies in Windows Server 2003, and is an integral part of server infrastructure such as ASP.NET and Internet Information Services. In addition to SQL Server 2005, Microsoft has begun to integrate .NET technologies into other server applications such as Commerce Server and BizTalk Server. It's clear that Microsoft has made a huge bet on the success of .NET.

Randy Holloway specializes in application server technologies, including SQL Server and BizTalk, in Microsoft's Central Region. He is also the author of the book "Developing Solutions with SQL Server 2005" from Addison-Wesley. Check out his weblog here.