Jeff Moden (5/28/2015)
Solomon Rutzky (5/28/2015)
But SQLCLR is far better than the alternatives of xp_cmdshell, the OLE Automation Procedures, extended stored procedures, and sometimes even OPENROWSET / OPENQUERY
Solomon should actually know better than to say things like he's said above because he took part in a rather lengthy thread that Solomon concerning performance where SQLCLR was put up against some SQL Server functionality and got beaten quite badly. Of course, there are other places where SQLCLR will beat SQL Server equally as badly. I'm not sour on SQLCLR by any means but I do think that people go a bit overboard with it simply because they don't know how to do certain things in T-SQL especially if the use of SQLCLR also comes at the price of performance. I'm also really tired of people that suggest that SQLCLR is "far better than alternatives" whether they be xp_CmdShell, extended stored procedures, etc, etc, because it's not a panacea. "It Depends". It's like the people that claim that the only way to do certain things is through SSIS and then they call a dozen stored procedures because SSIS can't do what needs to be done.
Nothing in this business is a panacea and when people like Solomon say something is "far better", they'd better be ready to prove it. He actually can't though. That thread I was talking about is proof of that and he really should know better than to make such broad and unsupportable claims.
Jeff, I think you are reading something into what I said that just isn't there. I neither said, nor implied, that SQLCLR was pancetta (it's not even kosher).
I've been involved in several threads related to SQLCLR vs "other stuff" and I have made it quite clear that, as much as I advocate SQLCLR, I even more so advocate using the right tool for the job. And I wasn't merely "involved in a thread" related to SQLCLR vs T-SQL performance, I published a study on the topic, CLR Performance Testing[/url], which a) shows T-SQL more often than not being A LOT faster than SQLCLR (inline, not UDFs), and b) concludes with "keep in mind that CLR is not always the best choice". And be sure to check out comment #3.
And yes, I did not go into explicit detail here regarding all of the qualifications of my "far better than" statement. But I did say "sometimes" better than OPENROWSET / OPENQUERY, and stated in my reply to Orlando that I had mis-stated xp_cmdshell and that it should also be in the "sometimes" category (I will go back and fix my original comment about that). Regarding the ways in which SQLCLR is better than some of those alternatives, I documented most of it here: Stairway to SQLCLR Level 1: What is SQLCLR?[/url]. And, I don't see a problem with saying that SQLCLR is "far better" than Extended Stored Procedures since, if nothing else, they have been deprecated since SQL Server 2005.
So, I agree that nothing (or almost nothing) in this business is Pangea (that is just silly and nonsensical). And again, I do not believe that I said or implied that SQLCLR was a magical, solves-all-problems, way to go. In fact, I agree with exploring the use of File Stream and File Table as they might be far better depending on the situation (though I still don't like the "OPENROWSET(BULK" method for this).
So hopefully that helps clear things up.
P.S. As for that "let's see if he takes the bait" thing, it was really late and I was being silly.