Recently I was at a UK user group meeting and Simon Sabin of SQL Skills presented a short “SQL Nugget” on UDFs and some of the issues with performance. He then showed a better way using a TVF and mentioned that in many cases the CLR can perform on par with system functions for simple code.

A person in the audience mentioned that it wasn’t that simple. Many companies might have concerns over deploying CLR code in assemblies to their SQL Servers. He cited that there was concern about actually having someone write “code” that runs inside SQL Server.

I’m concerned about the CLR, but not necessarily from a stability standpoint. As much as I think there could be performance issues, I don’t think that the code written by a developer or DBA and installed in SQL Server means that the core engine is necessarily less stable. Sure there may be issues with their code, but are they more serious than the issues of crappy client code? Or poorly written queries?

After all, we deploy code all the time on our servers, and it might be just as poorly written. I’ve seen sp_prepareXML cause issues, and I’ve seen XPs, provided by Microsoft, lower the stability of servers.

So is it just CLR being a black box that worries people? Do they have CLR concerns about .NET assemblies being deployed on IIS?

And if they are, then shouldn’t that mean that better training, code reviews,and extensive testing are needed?

The CLR can be a great choice in some cases. It’s a specialized tool, one worth looking at, but not necessarily the one for all situations. Just be aware that you may have a bit of a battle if you choose to use it on your SQL Servers.

If you have deployed the CLR, let me know how it went and what challenges you faced.