When To Use SQLCLR, And When Not To
Just because you can run the .NET 2.0 CLR in the SQL Server 2005 process doesn't mean that you should.
by Roger Jennings
Microsoft Professional Developers Conference, September 2005
It would be surprising if there were any .NET developers or SQL Server DBAs who aren't aware by now that SQL Server 2005 and its Express edition support an in-process implementation of the .NET 2.0 CLR. Early Microsoft whitepapers and marketing materials promoted the capability to execute managed code in the server process as the most significant new feature of the database that was then known as Yukon. Many of the first Web and print articles about Yukon encouraged SQL-challenged .NET developers to create database objects with VB or C# as an alternative to gaining proficiency with writing and executing T-SQL queries. A surprising number of the Web posts included a rhetorical question: Is T[ransact]-SQL Dead?
The jury's still out on the question of the importance of CLR integration to the ultimate commercial success of SQL Server 2005, either for new installations or as upgrades to SQL Server 2000. But the verdict is unanimous on developers' need for T-SQL competency. Regardless of their skill set for writing procedural VB or C# code, developers of data-intensive .NET projects must be able to author, execute, optimize, and debug complex T-SQL set-based code for traditional create-retrieve-update-delete (CRUD) operations. Developers in organizations where DBAs have total control of production and development databasesas well as write the T-SQL code for and manage all stored procedures and other database objectsmight represent exceptions. However, such an authoritarian scenario is uncommon in today's IT milieu.
The important questions for most data architects and developers of data-intensive .NET business layers or presentation tiers are if and when to replace traditional T-SQL statements with managed code. If your employer's or client's recalcitrant DBAs won't enable the CLR in "their" databases with the Surface Area Configuration for Features dialog, these questions are moot. Otherwise you'll probably find at least some of this article's guidelines will aid in making appropriate decisions for adding SQLCLR objects to production SQL Server 2005 databases.
Back to top