スポンサーリンク
技術情報

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

SQL CLRーVS/SQL Server 2017で使ってみた!
この記事は、SQL Serverで.NETで開発した関数が使えるって聞いたけど、どうやってやるの?に答えます。

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

SQL CLRとは

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

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

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

使用方法

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

SQL CLR使用手順
  1. .NETでSQL CLRのユーザ関数定義DLLライブラリを生成する
  2. SQL Serverのオプション設定
  3. SQL Serverユーザ関数定義の設定
SQL CLRーVS/SQL Server 2017で使ってみた!-イメージ図

.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の実行も問題なく実行完了。
SQL CLRーVS/SQL Server 2017で使ってみた!-実行結果

VS 2017の環境設定

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

インストーラー

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

SQL CLRーVS/SQL Server 2017で使ってみた!-VS2017インストーラー

プロジェクトの作成方法

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

SQL CLRーVS/SQL Server 2017で使ってみた!-新規プロジェクトを生成

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

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

SQL CLRーVS/SQL Server 2017で使ってみた!-新規ユーザ関数を追加

まとめ

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

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

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

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