SQLServerのCLR統合を使用してCLR関数を作成する

SQL Server 2005から提供されている.NET Frameworkの共通言語ランタイム（CLR: Common Language Runtime）とデータベースを統合する機能の「SQL CLR（CLR 統合）」を使用することにより、C#やVisual Basicなどで作成したアセンブリ（dll: Dynamic Link Library）をSQLServerで利用することができます。
SQL CLRではC#やVisual Basicなどで実装したアセンブリをデータベースに登録して使用します。登録したアセンブリはユーザー定義のストアド関数を作成することでSQLから呼び出すことが可能になります。

1 アセンブリ（dll）の作成
- 1.1 プロジェクトの作成
2 SQL Serverへのアセンブリの登録
3 CLR 関数の作成
4 CLR 統合の有効化
5 CLR 関数の実行
6 まとめ

アセンブリ（dll）の作成

SQL CLRを使用するために、Visual Studioでクラスライブラリのプロジェクトを作成してdllを作成します。
以下にSQL Serverで使用するためのdllを作成します。
ここでは例として、C#およびVisual Basicを使い文字列を前後（左右）に埋めるPadLeft関数とPadRight関数を作成します。

プロジェクトの作成

まずプロジェクトを作成します。プロジェクト名は任意の名前を付けることができます。ここではSqlClrPaddingという名前で作成します。
Visual Studioを起動したら、「ファイル」メニューにある「新規作成」>「プロジェクト」をクリックします。

「新しいプロジェクト」のダイアログボックスが表示されるので、プロジェクトのテンプレートの一覧から「クラスライブラリ (.NET Framework)」を選択し、プロジェクトの名前、作成する場所（フォルダー）、ソリューション名を入力して「OK」ボタンをクリックします。

C#の場合

プロジェクトが作成できたらPadLeft関数とPadRight関数をメソッドで作成します。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
// Microsoft.SqlServer.Serverをusing
using Microsoft.SqlServer.Server;

namespace SqlClrPadding
{
    public class SqlClrPadding
    {
        [SqlFunction]
        public static string PadLeft(string str, int totalWidth, char paddingChar)
        {
            return str.PadLeft(totalWidth, paddingChar);
        }

        [SqlFunction]
        public static string PadRight(string str, int totalWidth, char paddingChar)
        {
            return str.PadRight(totalWidth, paddingChar);
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

// Microsoft.SqlServer.Serverをusing

using Microsoft.SqlServer.Server;

namespace SqlClrPadding

{

public class SqlClrPadding

{

[SqlFunction]

public static string PadLeft(string str, int totalWidth, char paddingChar)

{

return str.PadLeft(totalWidth, paddingChar);

}

[SqlFunction]

public static string PadRight(string str, int totalWidth, char paddingChar)

{

return str.PadRight(totalWidth, paddingChar);

}

CLR 関数として使用するメソッドにはMicrosoft.SqlServer.Server名前空間のSqlFunction属性を指定します。

Visual Basicの場合

Imports System
Imports System.Data
Imports System.Data.SqlClient
' Microsoft.SqlServer.ServerをImports
Imports Microsoft.SqlServer.Server

Public Class SqlClrPadding
    <SqlFunction()>
    Public Shared Function PadLeft(
        ByVal str As String,
        ByVal totalWidth As Integer,
        ByVal paddingChar As Char
    ) As String
        Return str.ToString.PadLeft(totalWidth, paddingChar)
    End Function

    <SqlFunction()>
    Public Shared Function PadRight(
        ByVal str As String,
        ByVal totalWidth As Integer,
        ByVal paddingChar As Char
    ) As String
        Return str.ToString.PadRight(totalWidth, paddingChar)
    End Function
End Class

Imports System

Imports System.Data

Imports System.Data.SqlClient

' Microsoft.SqlServer.ServerをImports

Imports Microsoft.SqlServer.Server

Public Class SqlClrPadding

<SqlFunction()>

Public Shared Function PadLeft(

ByVal str As String,

ByVal totalWidth As Integer,

ByVal paddingChar As Char

) As String

Return str.ToString.PadLeft(totalWidth, paddingChar)

End Function

<SqlFunction()>

Public Shared Function PadRight(

ByVal str As String,

ByVal totalWidth As Integer,

ByVal paddingChar As Char

) As String

Return str.ToString.PadRight(totalWidth, paddingChar)

End Function

End Class

メソッドの実装が完了したら、プロジェクトをReleaseモードでビルドしてdllを作成します。

SQL Serverへのアセンブリの登録

プロジェクトをビルドしてdllが作成できたら、SQL Serverのアセンブリに登録します。
アセンブリの登録はCREATE ASSEMBLYステートメントを使って行います。

CREATE ASSEMBLY SqlClrPadding
FROM 'dllファイルを配置したパス'
WITH PERMISSION_SET = SAFE;

CREATE ASSEMBLY SqlClrPadding

FROM 'dllファイルを配置したパス'

WITH PERMISSION_SET = SAFE;

CREATE ASSEMBLYステートメントのFROM句に作成したdllのパスを指定します。

SQL Server 2017からは、CLR アセンブリのセキュリティを強化するために「clr strict security」というsp_configureのオプションが導入されました。
これにより、上記のSQLを実行すると、以下のエラーメッセージが表示されるようになりました。

sp_configure の ‘clr strict security’ オプションが 1 に設定されているため、SAFE または EXTERNAL_ACCESS オプションを指定したアセンブリ ‘SqlClrPadding’ の CREATE または ALTER ASSEMBLY が失敗しました。UNSAFE ASSEMBLY アクセス許可を持つ対応するログインを使用した証明書または非対称キーでアセンブリに署名することをお勧めします。または、sp_add_trusted_assembly を使用してアセンブリを信頼することができます。

MicrosoftのページにはCLR の厳密なセキュリティについて以下の記載があります。

警告
CLR では、セキュリティ境界としてサポートされなくなった、.NET Framework のコードアクセスセキュリティ (CAS) が使用されます。 PERMISSION_SET = SAFE で作成された CLR アセンブリが、外部のシステムリソースにアクセスし、非管理対象コードを呼び出し、sysadmin 特権を取得できる場合があります。 SQL Server 2017 (14.x) 以降、CLR アセンブリのセキュリティを強化するために clr strict security という sp_configure オプションが導入されました。 clr strict security は既定で有効になり、SAFE および EXTERNAL_ACCESS アセンブリを UNSAFE とマークされている場合と同様に扱います。 clr strict security オプションは、旧バージョンとの互換性のために無効にできますが、これは推奨されません。 Microsoft では、master データベースで UNSAFE ASSEMBLY アクセス許可が付与されている対応するログインを含む証明書または非対称キーで、すべてのアセンブリに署名することをお勧めします。 SQL Server 管理者は、データベースエンジンが信頼するアセンブリのリストにアセンブリを追加することもできます。

出典: CLR の厳密なセキュリティ

SQL Server 2017からCLR アセンブリのセキュリティを強化するために「clr strict security」というsp_configureのオプションが導入されているようです。

ですので、CREATE ASSEMBLYステートメントを実行する前に、データベースプロパティの「TRUSTWORTHY」をALTER DATABASE ステートメントを使用してONに設定します。
参考: TRUSTWORTHY データベースプロパティ

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
-- [DatabaseName]の部分に設定するデータベースの名前を指定してください。

1 2	ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON; -- [DatabaseName]の部分に設定するデータベースの名前を指定してください。

上記のSQLが実行できたらアセンブリをCREATE ASSEMBLYステートメントで登録します。

CREATE ASSEMBLY SqlClrPadding
FROM 'C:\example\SqlClrPadding.dll'
WITH PERMISSION_SET = SAFE;

CREATE ASSEMBLY SqlClrPadding

FROM 'C:\example\SqlClrPadding.dll'

WITH PERMISSION_SET = SAFE;

上記のSQLが正常に実行されると、データベースにアセンブリが追加されます。

CLR 関数の作成

アセンブリが登録できたら、アセンブリを使用したSQLのストアド関数を作成します。

CREATE FUNCTION PadLeft(
    @string NVARCHAR(MAX),
    @totalWidth INT,
    @paddingChar NVARCHAR(1)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrPadding.[SqlClrPadding.SqlClrPadding].PadLeft;
GO

CREATE FUNCTION PadRight(
    @string NVARCHAR(MAX),
    @totalWidth INT,
    @paddingChar NVARCHAR(1)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrPadding.[SqlClrPadding.SqlClrPadding].PadRight;
GO

CREATE FUNCTION PadLeft(

@string NVARCHAR(MAX),

@totalWidth INT,

@paddingChar NVARCHAR(1)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrPadding.[SqlClrPadding.SqlClrPadding].PadLeft;

CREATE FUNCTION PadRight(

@string NVARCHAR(MAX),

@totalWidth INT,

@paddingChar NVARCHAR(1)

)

RETURNS NVARCHAR(MAX)

AS EXTERNAL NAME SqlClrPadding.[SqlClrPadding.SqlClrPadding].PadRight;

関数の作成スクリプトでは、EXTERNAL NAMEの後に「アセンブリ名.[名前空間.クラス名].メソッド名」を指定します。

CLR 統合の有効化

SQL Serverでは共通言語ランタイム (CLR) 統合機能が既定でオフになっているので、CLR 統合を利用して実装されるオブジェクトを使用するには、CLR 統合機能を「1: 有効」にする必要があります。

Transact-SQLを使用してCLR 統合を有効にするには、システムストアドプロシージャのsp_configureを使用してclr enabledオプションを有効にします。

sp_configure 'clr enabled', 1
GO  
RECONFIGURE;
GO

sp_configure 'clr enabled', 1

RECONFIGURE;

clr enabledオプションを1に設定することで、SQL Serverでアセンブリを実行できるようになります。
（clr enabledオプションを0に設定することにより、CLR 統合を無効にできます。 CLR 統合を無効にすると、SQL ServerではすべてのCLR ルーチンの実行が停止され、すべてのアプリケーションドメインがアンロードされます。）

「sp_configure ‘clr enabled’, 1」を実行すると、「構成オプション ‘clr enabled’ が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。」というメッセージが表示されますので、続けてRECONFIGUREステートメントを実行します。

ちなみに、clr enabledオプションを1に変更していない状態でCLR関数を実行すると以下のメッセージが表示されます。

.NET Framework でのユーザーコードの実行は無効です。”clr enabled” 構成オプションを有効にしてください。

CLR 関数の実行

作成したCLR関数を実行してみます。

-- 作成したCLR関数を実行
SELECT
    dbo.PadLeft('123', 5, '0') AS PAD_LEFT,
    dbo.PadRight('123', 5, '0') AS PAD_RIGHT

-- 作成したCLR関数を実行

SELECT

dbo.PadLeft('123', 5, '0') AS PAD_LEFT,

dbo.PadRight('123', 5, '0') AS PAD_RIGHT

実行結果

.NETで実装した左文字埋（PadLeft）め、右文字埋め（PadRight）の処理が、SQLの関数として実行されて結果に反映されます。

まとめ

最後に.NETのアセンブリをCLR関数として使用する手順をまとめておきます。

.NET(C#, Visual Basicなど)でアセンブリ(dll)を作成する
ALTER DATABASE ステートメントで「TRUSTWORTHY」をONにする
CREATE ASSEMBLYステートメントで.NETアセンブリを登録する
CREATE FUNCTIONステートメントでCLR 関数を作成する
sp_configureプロシージャでCLR 統合を有効化（clr enabled = 1）する

従来から存在するストアドプロシージャや関数を、Transact-SQLではなく.NETの言語で記述できるSQL SereverのCLR 統合機能は実装の幅を広げてくれます。例えば正規表現を使用した置換処理などもC#やVisual Basicを使用して実装することができます。

CLR関数はSQL Serverのプロセスで実行されるので、ADO.NETなどの外部実行プログラムよりもデータアクセスのオーバーヘッドが小さいストアドプロシージャや関数のアドバンテージを保ちながら、.NET Frameworkの言語が使用できるのは大きなメリットです。特に外部リソースへのアクセスが可能になった点も大きな魅力のひとつです。