Michael J. Swart

July 27, 2016

Do you Use CLR in SQL Server?

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:11 am

We don’t use CLR assemblies in SQL Server. For us, programming in the database means that maybe “you’re doing it wrong”. But there have been rare circumstances where I’ve wondered about what the feature can do for us.

For example, creating a CLR assembly to do string processing for a one-time data migration might be preferable to writing regular SQL using SQL Server’s severely limited built-in functions that do string processing.

Deployment Issues

I’ve always dismissed CLR as part of any solution because the deployment story was too cumbersome. We enjoy some really nice automated deployment tools. To create an assembly, SQL Server needs to be able to access the dll. And all of a sudden our deployment tools need more than just a connection string, the tools now need to be able to place a file where SQL Server can see it… or so I thought.

Deploy Assemblies Using Bits

CREATE ASSEMBLY supports specifying a CLR assembly using bits, a bit stream that can be specified using regular T-SQL. The full method is described in Deploying CLR Database Objects. In practice, the CREATE ASSEMBLY statement looks something like:

CREATE ASSEMBLY [MyAssembly]
FROM 0x4D5A900003000000040000... -- truncated binary literal
WITH PERMISSION_SET = SAFE

This completely gets around the need for deployments to use the file system. I was unaware of this option until today.

Your Experience

So what’s your experience? My mistaken assumptions kept me from evaluating CLR properly. I wonder if anyone is in the same position I was in and I wonder if this accounts for the low adoption in general of CLR in SQL Server. Answer this survey, Which option best describes you?


External Link to Survey Monkey survey.

Update July 29 Here are the answers so far:
CLRAnswers

6 Comments »

  1. I admit that I discovered this a few years ago, by accident. I was working on a server migration for a server that used CLR. The CLR dependency had been long forgotten, so while back-tracking from the affected code, I scripted out the assembly in SSMS, and was pleasantly surprised that the generated script was all I needed to move the CLR–without needing to dig up the DLL.

    Comment by Andy Mallon — July 27, 2016 @ 11:23 am

  2. I’ve known about this for a while. When you ask VS to deploy an assembly from a DB project, it uses the ‘byte stream’ approach. Also, we use CLR for things like string splitting (i.e. turn a CSV into its constituent elements). Heck… I even deployed an CLR aggregate to calculate a median last year. So yes, we use CLR. Sparingly, but we use it.

    Comment by Ben Thul — July 27, 2016 @ 11:28 am

  3. The RedGate tools generate such scripts. That’s how I know. It’s the only way I deploy such assemblies.

    Since I use CLR purely for generic helpers such as string functions I can deploy new code out of band at any time. It does not need to be synchronized with other deployment operations.

    Yet, CLR always feels tedious and crippled somehow. Maybe that’s because T-SQL is tedious and crippled as a language and CLR cannot work around that.

    Comment by tobi — July 27, 2016 @ 4:02 pm

  4. […] Michael J. Swart wants to know if you’re using CLR in your environment: […]

    Pingback by CLR Survey – Curated SQL — July 28, 2016 @ 8:00 am

  5. That’s good info Andy, Ben, tobi,

    It really helps me to get a sense of how others use (or don’t use) this tool.

    Michael

    Comment by Michael J. Swart — July 29, 2016 @ 9:36 am

  6. I have been reluctant to use CLR because of the (possibly outdated) perception that it had the potential to cause memory leaks and other such issues. But I haven’t looked into it since SQL Server 2005.

    Comment by Mark Freeman — August 1, 2016 @ 10:09 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress