SQL Server 2008 CLR vs T-SQL: Is there an efficiency/speed difference?

Question

I'm a C# developer who has done some basic database work in T-SQL. However, I need to write a very complicated stored procedure, well above my T-SQL knowledge.

Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL? Is the difference (if any) significant? Why?

Answer 1

Please see Performance of CLR Integration:

This topic discusses some of the design choices that enhance the performance of Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR).

Answer 2

CLR require some communication overhead (to pass data between the CLR and SQL Server)

Rule of thumb is:

• If your logic mostly includes transformations of massive sets of data, which can be performed using set operations, then use TSQL.

• If your logic mostly includes complex computations of relatively small amounts of data, use CLR.

With set operations much more can be done than it seems. If you post your requirements here, probably we'll be able to help.