SQL CLRーVS/SQL Server 2017で使ってみた！

.NET側での開発方法とSQL Server側での設定・登録方法をご説明します！

SQL CLRとは

.NETで開発したDLLライブラリを登録する事で、【ユーザ関数をSQL Serverで使用できる】ようにしたもの。

ストアドプロシージャや、ユーザー定義関数から呼び出す事ができます。

SQL Server 2005 から使用可能で、『CLR統合機能』ともいいます。

使用方法

SQL CLR機能を利用する為には、DLLライブラリを開発しSQL Server側で登録する必要があります。

.NETでユーザ関数を開発する

SQL CLRのスカラ関数用定義を追加します。
※サンプルでは戻り値を”TEST”に変更します。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SqlFunction1()
    {
        // コードをここに記述してください
        return new SqlString ("TEST");
    }
}

SQL Serverのオプション設定

サーバ単位設定の『clr enabled』オプションをONにします。

EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;

SQL Serverのユーザ関数設定

DLLライブラリ登録の「アセンブリ登録」と、関数の作成「スカラ関数登録」を行います。

アセンブリ登録

ALTER DATABASE TEST SET TRUSTWORTHY ON;

CREATE ASSEMBLY SqlClr
FROM 'C:\Temp\SqlClr.dll'
WITH PERMISSION_SET = SAFE;

スカラ関数登録

CREATE FUNCTION SqlFunc()
RETURNS NVARCHAR(MAX)   
AS EXTERNAL NAME SqlClr.UserDefinedFunctions.SqlFunction1;

サンプルの実行結果

作成したサンプルの実行結果。

T-SQLの実行も問題なく実行完了。

VS 2017の環境設定

VS2017でのプロジェクト生成が少し癖があるので、解説します。

インストーラー

『Workload』－『Data Storage and　Processing』－『SQL Server　Data　Tools』をインストールする

プロジェクトの作成方法

・「ファイル」－「新規作成」－「プロジェクト」
・「SQL Server」－「SQL Serverデータベースオブジェクト」で新規プロジェクトを生成します。

ユーザ定義関数の追加方法

・「プロジェクト」－「新しい項目の追加」
・「SQL Server」－「SQL CLR C#」－「SQL CLR C#ユーザ定義関数」で新規ユーザ関数を追加します。

まとめ

SQL CLR機能はパフォーマンスの遅さから使用用途が限られるように思います。

あくまでSQLのハードルが高い人で.NETが組めるよというコンセプトで当初作られたのもあり、普及が進まなかった背景があります。

SQLでは正規表現処理など.NETでしか使えない機能をDBで使えるメリットはあります。

使用方法にとってはメリットにもなりえるので、おすすめです！