SQL Server Solutions - When To Use CLR Integration For Stored Procedures In SQL Server 2005: Part 1 (SQL Server 2005)
As you've probably been hearing more and more, SQL Server 2005 (formerly code-named Yukon) provides the ability to write stored procedures in .NET languages, such as Visual Basic .NET or C# in addition to T-SQL. But, as with most competing solutions, there are pros and cons to using one approach or the other in a given situation. In our next several tips, we'll offer a few insights into choosing between a purely T-SQL implementation versus a .NET CLR-based approach.
For today, if the logic behind your stored procedure consists mainly of fairly direct and simple DML statements (INSERT, SELECT, UPDATE, and similar operations), you'd probably do better to develop the code using T-SQL. The reason is that CLR-based implementations require additional overhead, as they must communicate back and forth between the CLR and SQL Server, marshalling data between them. This overhead can easily add up to much more than the processor time required for straightforward DML.