SYSK 68: Choosing Between T-SQL and .NET Stored Procs

The official recommendation is relatively well known by now, and is stated below:

Transact-SQL is best for situations in which the code will primarily perform data access with little or no procedural logic. Programming languages that are compatible with the .NET Framework are best suited for computationally intensive functions and procedures that feature complex logic, or for situations in which you want to take advantage of the .NET Framework class library.
 
Code placement is also important. Both Transact-SQL and managed code run on the server. This places functionality and data close together, and allows you to take full advantage of a server's processing power. This is beneficial because it decreases traffic between the data and middle-tier. By simply performing the computation next to the data, a system that is I/O-bound may find this method significantly beneficial. CLR functions also can take advantage of the SQL Server query processor, which can parallelize and optimize execution. On the other hand, you may wish to avoid placing processor intensive tasks on your database server. Most client computers today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client. There is no "one size fits all" answer.

Source: "Writing Effective Data Access Applications Using SQL Server 2005" training

The one unanswered question in the guidance above is about passing large amounts of data into the stored proc -- the old chunky vs. chatty approach...  In the past, the options were to use a delimited string shredded in the SQL stored proc, or using OpenXML.  With CLR hosted in SQL, we now have one more option -- passing a .NET class to a .NET stored proc.  I put this idea to the test, and the results (tested across a network with SQL box being 2 Ghz/1Gb Win2003 Server) are this:
      Chunky (shredding done in SQL)  =>  23 ms/iteration
      Chatty  =>  31 ms/iter
      CLR stored proc with data passed as .NET class  =>  28 ms/iter
      CLR stored proc (shredding done in CLR stored proc)  =>  22 ms/iter
      OpenXML  =>  22 ms/iter

While I always recommend to do your own tests before making any cornerstone architectural decisions, it's certainly looks like performance gains/losses are negligible across all "chunky" methods...  So, unless you’re CPU bound on SQL server and would rather sacrifice performance for CPU utilization reprieve, go for the ease of maintenance and extensibility!  Go either with .NET types passed to a stored proc, use OpenXML or the new xml data type.