This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
Regardless of whether you are familiar with SQL CLR, or are looking to explore the world of opportunities it offers, this article is for you. Our focus will be on embedding C# code inside SQL Server and Azure SQL Managed Instance engines. We will also talk about how you can run CLR seamlessly, regardless of where you are running your SQL instance - on-premises, in Azure, or using a hybrid environment.
First, a quick reminder of where the name comes from: CLR stands for Common Language Runtime, which is a Runtime Engine that, among other things, executes your C# code. But the nice thing about it is that it’s not limited to C#. It also executes the code written in VB.NET, C++/CLI (Managed C++), F#, and similar. This is why it’s called “Common Language Runtime”.
SQL CLR allows you to embed your CLR code directly alongside the SQL Server’s Query Engine, which is really nice because you can execute your code directly at the data source, without the need to do any data transfer to your application layer.
Here are some of the interesting things that you can do with it:
- You can create User-Defined Functions (UDFs) and execute them as part of your queries. Think of it as being able to write something like SELECT FOURIER_TRANSFORM(raw_data) FROM table or SELECT a, b FROM table ORDER BY VOLUME(a). In this case, the FOURIER_TRANSFORM and VOLUME are UDFs – functions written in any CLR supported language (e.g. C#).
- You can create User-Defined Types (UDTs) and use them when creating tables. Think of it as defining a structure in C# and using it as one of the columns. For example, you could define a Tree structure in C#, use it as one of the Data Types, and then do something like SELECT tree_column.GetChildren(), tree_column.GetParent() FROM table.
Let's now discuss how to import your assemblies into SQL Server or Azure SQL Managed Instance. As a reminder, assemblies represent basic deployment units in CLR. Both EXEs and DLLs are considered to be assemblies. They carry your code, along with a metadata needed to make it executable. Here is an image that demonstrates the process of creating an assembly:
Once you have the assembly, the next step is to import it into your instance. The importing process varies slightly, based on whether you are using on-premises or Azure version. We will cover both.
Here's how to import the assembly into SQL server:
For Azure SQL Managed Instance you need to provide the assembly bits directly in T-SQL code. Here is how to do it using PowerShell:
And that’s it! Your assembly was successfully imported. To learn more about using CLR, I suggest checking our official documentation.
Speaking of documentation, one thing that you might notice is the CAS Deprecation warning. CAS (Code Access Security) was a .NET framework security feature that allowed you to fine-tune the access requirements that the CLR Assembly might need. Specifically, in terms of SQL Server and Azure SQL Managed Instance, this meant that, during the creation of assembly, you have to specify one of the three permission sets – SAFE, EXTERNAL_ACCESS or UNSAFE.
SAFE is the most restricted level that prevents your Assembly from accessing external system resources such are files, network, environment variables, or the registry. EXTERNAL_ACCESS is a bit more flexible option and allows you to access files and the network, but still prohibits you from doing anything that might endanger the system. Finally, UNSAFE, as the name implies, allows your code to do pretty much anything allowed by .NET and CLR. You can read more about these permission option sets here.
Due to deprecation of CAS, all previously created assemblies are considered to be UNSAFE. In order to enable backward compatibility, a new configuration option called “clr strict security” has been introduced. It is set to “1” by default, meaning that EVERY new assembly will be treated as UNSAFE. To override this behavior, you can set it to “0”. Even though it is possible, due to security risks involved, we do not recommend doing it. You can learn more about it here.
We hope you enjoyed the content and if you want to learn more, we suggest checking some of the resources below:
P.S. We would love to hear your feedback! If you enjoyed this article, or think there might be some improvements to be made, we would appreciate if you leave your comment below. Thanks for reading!