Press "Enter" to skip to content

An Overview of CLR

Chad Callihan gives us the primer:

I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a vague error in the application and still didn’t know the exact cause. I fired up an XEvents session to find the query causing the issue and found a query failing with the following error:

Msg 6263, Level 16, State 1, Procedure dbo.MyProc, Line 60 [Batch Start Line 14]
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Incidentally, my single biggest gripe around CLR integration in SQL Server (and something I complain about almost every time someone brings up the feature) is that they really messed up in picking names. CLR has Safe and Unsafe modes.

What the terms mean:

  • Safe = managed mode. Your .NET language of choice (at the time, VB.Net or C#) handles pointers for you, so you can’t dereference a null pointer. Well, you still can but will instead get an “Object reference not set to an instance of an object” exception rather than some potentially unexpected behavior
  • Unsafe = developers control pointers. There were some things, especially in 2005, that made sense to create in Unsafe mode because it was considerably faster

What far too many DBAs interpreted it as: “I don’t want unsafe code running in my systems! Ban CLR!”

This fundamental misunderstanding of terms killed a smart integration and capability for extremely fast functions—for example, the fastest way to split a string in T-SQL is via CLR, and there’s a lot of impressive functionality you can get at near-native speeds (i.e., if the SQL Server team wrote the code as a function instead of you, allowing them to optimize the database engine for that function) with a few lines of code. But the leads at most places saw the word “Unsafe” and nope’d out.