Click on the banner to learn about and purchase my database training on Azure

Introduction to SQL Common Language Runtime (CLR) in SQL Server

Views: 6.389
Reading Time: 14 minutes

Hello people,
Good Morning!

In this post, which will be the blog's 100 number, I'd like to talk about something I particularly like about SQL Server, which is the creation of .NET routines within the SQL Server database. Yes, we are talking about the common language runtime (CLR).

Introduction

Click here to view
Present in the SQL Server database since the 2005 release, CLR is the heart of the Microsoft .NET Framework platform languages. Code that runs inside the CLR is known as managed code. The CLR provides several functions and services required for program execution, including just-in-time (JIT) compilation, memory allocation and management, type security enforcement, exception handling, thread management, and security.

In other words, CLR allows you to be able to create routines (stored prodecures, functions, triggers, etc.) written in C #, F # and VB.NET, compile and execute them in the database natively, extending DBMS capabilities. , because you can create a multitude of things that wouldn't be possible using just Transact-SQL, such as file manipulation, FTP file upload and download, aggregate functions, Webservices integration, and more.

Advantages and disadvantages

Click here to view
As with any technology and tool, there are always strengths and weaknesses where you should or should not use the CLR. I will list some:

SQL CLR Advantages

  • Possibility of creating new features that would not be possible using only T-SQL
  • Ability to work with regular expressions (RegExp) in the database
  • Performance Optimization: The same function written in C # in CLR is usually performed much faster than a T-SQL function, especially in cases of looping and calculations, since the .NET compiler is specialized for this type of operation, while T-SQL is for working with collections. In my work, I've seen several cases where the same CLR function executed 5x, 10x and some even 60x faster than the T-SQL function.
  • Webservices integration via database
  • Security: A nice thing I like about the CLR is that we can define a fixed user to connect to the database. In this way, we can free access for him to execute SP's and consult views and system tables and create functions and SP's in the CLR for that. When an analyst needs to use these system SP's, simply release access to the CLR's SP / View / Function that will have indirect access to the system object, without having to release access to it in the source object or create objects in system banks
  • Development Tools: The tool used to develop CLR routines is Visual Studio. Management Studio is a very good IDE for creating Transact-SQL routines, especially as SQL Prompt is installed, but it doesn't compare to powerful Visual Studio, especially ReSharper. Programming is much faster and practical.
  • Source Code Versioning: Because we are using Visual Studio, source code can be easily controlled and managed by Team Foundation Server (TFS), giving full control to the code created, unlike Stored Procedures in the database, which does not have controls like merge, diff, etc.
  • Xp_cmdshell Override: Although disabled by default, many people and businesses end up enabling cmdshell on their instances, even in production, as some operations cannot be performed using only Transact-SQL, such as file manipulation. , for example. This feature is a great danger since it simply executes any command that is sent to it without any filter or restriction. To do so, I recommend disabling this feature and using CLR procedures designed solely for each purpose, be it a copy of files or even upload an instance.
  • OLE Automation replacement: This feature is still widely used and is also disabled by default, OLE Automation procedures are C ++ libraries that allow you to use Windows APIs to perform various operations, such as file manipulation, etc. The big problem is that by enabling this feature, any user can create anything with this, coupled with the fact that the commands are not managed and are executed within the SQL Server process. In case of failure, the instance is shut down, as the SQL Server process is closed automatically by the operating system (!!!!)
  • Automation: With CLR procedures, you can automate a multitude of day-to-day processes, which previously could only be automated using Integration Services, which is a great tool, but ends up becoming somewhat limited in the face of the world of CLR possibilities , since in MSIS you only have the resources that tools provide you, while in the CLR you can create anything that the .NET platform allows. In addition, the results of the CLR are database objects, whether Stored Prodecures, Functions, Triggers, etc., which can be used freely in other SP's, Jobs, and any other object in the bank, while Packages can only be executed by the tool or Jobs. (Note: CLR and MSIS are tools with different objectives, I just compared it because some Integration Services tasks can be easily replaced by the CLR)
  • Connectivity: Possibility to use the .NET Framework connectors and access other DBMS's and other instances with a direct link, without the need for LinkedServer, which executes the command remotely

Disadvantages of SQL CLR

  • Need knowledge in SQL and programming language (C #, F # or VB.net)
  • Little documentation and knowledgeable people
  • When you publish a new version, objects are removed and recreated, losing permissions and making objects unavailable during publication.
  • If it is poorly developed and implemented, it may present a risk to the DBMS.
  • Some functions may require a high CPU volume for processing.
  • There are no optional parameters for procedures. All must be completed.

Enabling CLR on Your SQL Server Instance

Click here to view
By default, CLR is disabled on SQL Server instances. To enable this feature is quite simple, just use the command sp_configure:

And we will have the following result:
Enabling CLR on SQL Server

Otherwise, you will come across this error message when trying to use the CLR:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Creating Your First SQL CLR Project in Visual Studio

Click here to view
The first step in building our CLR project is of course, downloading and installing Visual Studio compatible with your version of SQL Server (I recommend Visual Studio 2015 Community Editionas it is compatible with SQL Server 2005 through 2014 and free for developers).

After that, open Visual Studio and access the File> New> Project menu. Select Project Type SQL Server> SQL Server Database Project

New CLR Project

And it should look like this initially:
CLR new project - Solution explorer

Now let's add a new class library project that will contain our C # codes. To do this, right-click on Solution and select Add> New Project. Select Visual C # Category> Windows> Class Library

CLR New Class Library

Once created, I usually create directories by object type in the Class Library for better code organization. This is recommended, but optional. You may want to organize your code by subject or as needed.

Some references can be removed, as they will not be used in the examples. Right click on “References” of the CLR project and select the option “Add Reference…”. On the screen that will open, select the Projects> Solutions category and check the Class Library project checkbox:

CLR Add Reference

Be sure to set the permission of the imported project as shown in the figure below:
CLR Assembly Permission Set 2

Solution Explorer should look like the following:
CLR new project with Class Library - Solution explorer

Creating a stored procedure with no return

  • Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file stpCopia_Arquivo.cs
  • Copy and paste the code below:

Creating a stored procedure with return of a select

  • Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file stpImporta_Txt.cs
  • Copy and paste the code below:

Creating a scalar function

  • Right click on the “Functions”> “Scalar Function” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opened, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file fncArquivo_Existe.cs
  • Copy and paste the code below:
  • Note that, unlike procedures that always return void, functions must return data. In the example case, return data of type SqlBoolean (bit = true / false)

Creating a table-valued function

  • Right click on the “Procedures” directory of the Libraries project (Class Library) and select the option Add> Class… On the screen that opens, type the name of the file that will be created. I usually put the same name as the object that will be created in the database. In this example, I will create the file fncArquivo_Ler.cs
  • Copy and paste the code below:
  • This type of object is probably the most laborious to create in the CLR, since its result is a table and to populate that table we need to create a class with sets / gets with the data to be returned, set the output of data to the bank and programming to populate / calculate the data.

string or SqlString?

Click here to view
As you may have noticed from the examples above, in some cases I use string, bool, int, etc .. and in others I use SqlString, SqlBoolean, SqlInt32, etc. This difference is that the first cases are data types from C #, while others are database-driven and have more controls and ways to work.

Examples:
The SqlString type has the IsNull check method to determine if a NULL value has been passed by parameter. This check is faster than using the string.IsNullOrEmpty. However, using a string variable as a parameter, if the function or SP is called with a NULL value, it will be executed normally, whether or not it will be treated in the future in its source code. If you enter a NULL value for a variable of type SqlString and you have not handled it using the variable.IsNull method, retrieving the value entered using the variable.Value attribute or the variable.ToString () method will raise an exception. of ERROR in your routine.

Another nice example to illustrate the difference is between DateTime and SqlDateTime. If you pass a NULL date to DateTime, an ERROR exception is raised in your routine. This data type does not accept NULL. If you need to use something like this, you should use the MinValue (1 / 1 / 0001 12: 00: 00 AM) or MaxValue (31 / 12 / 9999 23: 59: 59) method. Already the data type SqlDateTime accepts null values ​​and has the variable.Null method to define null values.

My recommendation is to always use database data types (SqlString, SqlInt32, etc.) and ALWAYS remember to perform the necessary treatments (especially the variable.IsNull).

Which version of the .NET Framework should I use?

Click here to view
Identifying and deciding which version of the .NET Framework to use is quite a simple task, but it has caused me a lot of headache in the past due to poor documentation on the subject. Therefore, I will try to help you by simplifying:
- SQL Server 2005: You can only use the .NET Framework 1.0 and 2.0
- SQL Server 2008: Supports up to .NET Framework 3.5
- SQL Server 2012 and 2014: Support up to .NET Framework 4.6.1

You can define the .NET Framework version when creating the project or by right-clicking on the Class Library project and selecting the “Properties” option. You need to do the same procedure for the CLR project as well, which still gives you the possibility to define the database version:

CLR - Target Framework

CLR - Target Platform

CLR Class Library - .NET Framework Version

Compiling your project and publishing to the database

Click here to view
Once the code has been developed, we will publish the generated assemblies in the database to start using the objects that will be created. You can set Visual Studio itself to create the target database (if it doesn't exist) or you can manually create the database first (I recommend this option) and then publish the assemblies.

Once the database has been created, right-click on the CLR project and select the “Publish…” option. The "Build" and "Rebuild" buttons are used to only compile the source code and already validate if there are syntax errors in the source, while the "Clean" button eliminates the generated files and the Visual Studio cache.

CLR - Publish

On the screen that will open, you must click on the “Edit…” button to enter the server and how to connect to the database.

CLR - Publish Database 2

You can create / upload profiles to facilitate publication in more than one environment. The “Generate Scripts” button will generate an SQL script, which must be executed by SQLCMD to publish the CLR and the “Publish” button will generate the script and already run in the database.

CLR Publish completed successfully

Assembly Permission Level

In SQLCLR, there are 3 permission levels of created assemblies:

  • SAFE: Assembly methods can do more than Transact-SQL methods with the same logic and are executed with the credentials of the calling user.
  • EXTERNAL ACCESS: Methods can perform file manipulation and I / O operations over the network. Methods are executed using the SQL Server service account, inheriting their Active Directory privileges.
  • UNSAFE / UNRESTRICTED: Extends EXTERNAL ACCESS privileges, allowing CLR to execute commands without any restrictions

If the assembly needs to use EXTERNAL ACCESS or UNRESTRICTED permission levels, you must set the database to which it will be published as TRUSTWORTY:

Required Permissions to Compile the CLR

In order for a user to have permission to publish a CLR to the database, they must meet one of the following requirements:

  • Role Member sysadmin
  • If the assembly permission level is SAFE, then the user will need CREATE ASSEMBLY and DROP ASSEMBLY permission.
  • If the assembly permission level is EXTERNAL ACCESS, then the user will need CREATE ASSEMBLY, DROP ASSEMBLY, and EXTERNAL ACCESS ASSEMBLY permission.
  • If the assembly permission level is UNRESTRICTED, then the user will need CREATE ASSEMBLY, DROP ASSEMBLY, and UNSAFE ASSEMBLY permission.

Final result:

CLR - Object Explorer

SQLCLR

Assembly restrictions: supported and unsupported DLLs

Click here to view
SQL Server places certain constraints on managed code in assemblies to verify that they can be performed in a safe and scalable manner. This means that certain operations that may compromise server robustness in SAFE and EXTERNAL_ACCESS assemblies are not allowed.

Custom attributes not allowed
Assemblies cannot be annotated with the following custom attributes:
- System.ContextStaticAttribute
- System.MTAThreadAttribute
- System.Runtime.CompilerServices.MethodImplAttribute
- System.Runtime.CompilerServices.CompilationRelaxationsAttribute
- System.Runtime.Remoting.Contexts.ContextAttribute
- System.Runtime.Remoting.Contexts.SynchronizationAttribute
- System.Runtime.InteropServices.DllImportAttribute
- System.Security.Permissions.CodeAccessSecurityAttribute
- System.STAThreadAttribute
- System.ThreadStaticAttribute

Additionally, SAFE and EXTERNAL_ACCESS assemblies with the following custom attributes cannot be annotated:
- System.Security.SuppressUnmanagedCodeSecurityAttribute
- System.Security.UnverifiableCodeAttribute

.NET Framework disallowed APIs
Any Microsoft .NET Framework API noted with one of the HostProtectionAttributes bans cannot be called from SAFE and EXTERNAL_ACCESS assemblies.
- eSelfAffectingProcessMgmt
- eSelfAffectingThreading
- eSynchronization
- eSharedState
- eExternalProcessMgmt
- eExternalThreading
- eSecurityInfrastructure
- eMayLeakOnAbort
- eUI

Supported .NET Framework Assemblies
Any assembly referenced by your custom assembly must be loaded into SQL Server using CREATE ASSEMBLY. The following .NET Framework assemblies are already loaded in SQL Server and therefore can be queried by custom assemblies without having to use CREATE ASSEMBLY.
- CustomMarshalers.dll
- Microsoft.VisualBasic.dll
- Microsoft.VisualC.dll
- mscorlib.dll
- System.dll
- System.Configuration
- System.Core.dll (supported from SQL Server 2008)
- System.Data.dll
- System.Data.OracleClient
- System.Data.SqlXml.dll
- System.Deployment
- System.Security.dll
- System.Transactions
- System.Web.Services.dll
- System.Xml.dll
- System.Xml.Linq.dll (supported from SQL Server 2008)

Catalog Views and SP's

Click here to view

Video - Introduction to SQLCLR

Want to chat with us about SQLCLR?
- Telegram: https://t.me/sqlclr
- Whatsapp: https://chat.whatsapp.com/71JS49CiD12Ct6pR3fjfLu

And that's it folks!
Until the next post!

SQL Server CLR SQLCLR how to enable enable how to use how to enable enable how to create sp how to create procedure how to program how to get started getting started where to how to code coding programming procedures table-valued functions scalar c # csharp programming database database programming

SQL Server CLR SQLCLR how to enable enable how to use how to enable enable how to create sp how to create procedure how to program how to get started getting started where to how to code coding programming procedures table-valued functions scalar c # csharp programming database database programming