RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Choosing Between the CLR and T-SQL in SQL Server 2005

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.

icrosoft's integration of the .NET Framework and the CLR directly into the database engine in SQL Server 2005 is a key enhancement. The result is a major paradigm shift for database application developers and administrators, who can leverage the new functionality in many different ways. In this article, you'll gain insight into this new programmability architecture, and get some tips on how to choose between the new features and the familiar T-SQL language when building your applications.

SQLCLR is Microsoft's name for the integration of the CLR as a hosted runtime within SQL Server, enabling the execution of managed code inside the database. SQL Server 2005 is highly integrated with the .NET Framework, letting you create stored procedures, functions, user-defined types, and user-defined aggregates using your favorite .NET programming language. All these constructs can take advantage of large portions of the .NET Framework infrastructure, the base class library, and third-party managed libraries.

In many cases, managed code intended for execution within SQL Server will be very computation-oriented. Functions such as string parsing or scientific math are quite common in the applications that early adopters have created using SQLCLR; however, you can do only so much using number-crunching or string manipulation algorithms in isolation. At some point you have to get input or return results. If that information is relatively small and granular you can use input and output parameters or return values, but if you're handling a large volume of information then using in-memory structures isn't an appropriate representation/transfer mechanism—a database might be a better fit in those scenarios. If you choose to store the information in a database then SQLCLR and a data-access infrastructure are the tools you'll need. And the tool that you'll need for data access is ADO.NET.

Because ADO.NET "just works" inside SQLCLR, you can leverage all your existing knowledge of ADO.NET to get started. As an example, take a look at the code snippet below. This code would work equally well in a client-side application, Web application or a middle-tier component. It will also work inside SQLCLR.

What You Need
You should have an understanding of the .NET Framework and C# or VB.NET to build the samples included with this article. The tools required to build the sample project are the April CTP of SQL Server 2005 and Whidbey Beta 2.

   using(SqlConnection conn = new SqlConnection(
      "server=MyServer; database=AdventureWorks; " + 
      "user id=MyUser;  password=MyPassword")) 
         SqlCommand cmd = new SqlCommand(
            "SELECT Name, GroupName FROM " +  
            "HumanResources.Department", conn);
         SqlDataReader r = cmd.ExecuteReader();
            // Consume the data from the reader and 
            // perform some operation/computation with 
            // it.

The preceding snippet uses the System.Data.SqlClient provider to connect to SQL Server. Note that if this code runs inside SQLCLR, it would be connecting from the SQL Server that hosts it to another instance of SQL Server, but you can also connect to other data sources. For example, you can use the System.Data.OracleClient provider to connect to an Oracle server directly from inside SQL Server. For the most part, there are no major differences using ADO.NET from within SQLCLR. However, what happens if you want to connect to the same server your code is running in to retrieve or alter data? In that scenario, you use the "context connection."

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date