As the release of SQL Server 2005 approaches, many DBAs and developers have concerns and questions about its new features. And nothing has generated more concerns and misinformation than SQL Server 2005's Common Language Runtime (CLR) integration. Let's clear up some common questions and concerns about SQL Server 2005's CLR integration.

First, if you're uncomfortable with the CLR, you can turn off SQL Server 2005's CLR support. You enable and disable the use of the CLR through the clr_enabled server-configuration option. This is an advanced configuration option, so you need to execute sp_configure 'show advanced options' before you can set the clr_enabled option. Then, you can run sp_configure 'clr_enabled', 0 to turn off CLR support. No server restart is required. All the normal database functions continue, but you can't create and execute CLR database objects. Currently, Microsoft plans to ship SQL Server 2005 with CLR support turned off, but this plan might change by the final release.

Next, T-SQL isn't going away. Microsoft added CLR support to SQL Server to extend SQL Server's functionality, not to make you convert all your stored procedures from T-SQL to C#. SQL Server 2005 remains a relational database management system (RDBMS), and SQL (or in this case, T-SQL) is the industry-standard language for retrieving and manipulating relational data. T-SQL will remain the primary method of manipulating SQL Server data. In fact, Microsoft added several T-SQL enhancements to SQL Server 2005. For an in-depth look at these enhancements, check out Itzik Ben-Gan's T-SQL 2005 column at

Will CLR integration make knowing T-SQL unnecessary? If you've used ADO.NET before, you already know the answer. ADO.NET is required for building CLR objects. But although ADO.NET is an object-oriented data-access framework, it uses T-SQL to retrieve and manipulate data. When you create an ADO.NET SqlCommand object, you also need to set the object's CommandText property to supply that object with a database command—a T-SQL command.

It's true that to create the new CLR database objects, you need to program. Visual Studio 2005 (code-named Whidbey) contains enhancements to help you build and deploy CLR database objects. You use Visual Studio 2005 first to create a new SQL Server project, then to compile that project into a DLL and deploy it to SQL Server 2005. Although this process requires coding in a .NET language such as Visual Basic .NET (VB.NET) or C#, you'll find that the syntax isn't much more complicated than T-SQL's. The main challenge will be learning how—and when—to use the ADO.NET classes to create your CLR database objects.

So, when should you use the new .NET database objects and when should you stick with T-SQL? You can best use the new CLR enhancements to replace extended stored procedures or when you need access to external system resources or for situations requiring complex logical operations or math. With its built-in classes, the .NET Framework gives you plenty of functionality for programmatically accessing external resources. And because CLR objects are compiled, they run faster than those written in T-SQL, which is interpreted. But T-SQL is still best for data-access operations. So don't worry about CLR integration. The CLR won't take anything away from SQL Server—it will let you extend SQL Server applications like never before.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.