The trivialization of SQLCLR just doesn't make sense to me
Note: updated with edits in-line. Sorry for the confusion on my part, Don.
Don Demsak and Sahil Malik got me started on this thread over in Don's Blog: First Rule Of CLR Stored Procedures
Like Sahil, I don't completely agree with the Kimberly Tripp's Don's first rule -- If you would not have built it as an extended stored proc in SQL Server 2000, odds are that you don't need to create a CLR Stored Proc in SQL Server 2005 -- and mostly for the same reasons. I do agree that SQLCLR-based Stored Procedures are preferable to XPs in many ways (context connections and participation in transactions are good cases). And frankly, I've never had much use for SQLCLR-based stored procedures themselves because the work you are typically trying to do with them is commonly more efficiently and effectively done with T-SQL.
Note that I do agree with what Kimberly Tripp said -- SQLCLR and XML were going to be the most abused features of SQL 2k5. I really do worry that folks are going to create non-sustainable solutions with these technologies simply because the choose not learn other ways of doing things that are better implemented with traditional SQL Server technologies and patterns. UDTs are a huge worry because its so easy to see them as a silver bullet to building an Object Database. I do worry about people over using XML to store data that's better processed in a normalized form. But what I worry most about is people refusing to learning as much as they can about both so they can make informed choices. Its all well and fine for folks like Bob, Kimberly, Adam and myself to blather on about the technology and split hairs over it. I don't think its a bad thing to learn from discussions like that. At the same time, that shouldn't be your only basis of knowledge. Get into the technology and try it for yourself. Understand it as only you can understand it.
The important thing here is, I believe, that SQLCLR Stored Procedures are just a part of SQLCLR brings to the table. Yet it seems to be a lot folks that are willing to trivialize SQLCLR in general simply based on that. They can't seem to see use cases beyond stored procedures. But as I see it, the real leverage that SQLCLR offers in making more efficient, easier to develop and sustain User Defined Functions. Clearly they can make doing some data transformations much cleaner and more performant. They also let you leverage the depth and width of the .NET framework which is something T-SQL just can't do. The best code you ever write is code you never write.
I'm not such a fan of other parts of the SQLCLR space. User Defined Types are probably useful if used to define new scalar types, but these are where the real dangerous parts of SQLCLR are in my mind for the unwary. That people will write SQLCLR stored procedures when they shouldn't does even begin to phase me versus the potentially disastrous mapping of domain objects to SQLCLR types. Aggregators seem useful, I suppose, but the only time I've really had a good "business" case for one is atypical and was based on the use a SQLCLR UDT. SQLCLR triggers seem close enough to stored procedures that I see them as only a small part of the whole story.
So what's a better first rule? I'll suggest "If a Stored Procedure can be written in T-SQL, exhaustively try that first. If you find that its too slow or your organization lacks the ability to sustain it going forward, then consider using a SQLCLR Stored Procedure. Test and compare performance carefully, document well."