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

SQLServerロゴ

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から呼び出すことが可能になります。

アセンブリ(dll)の作成

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

プロジェクトの作成

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

Visual Studio プロジェクトの新規作成メニュー

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

C#の場合

C#のプロジェクトの新規作成

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

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

Visual Basicの場合

Visual Basicのプロジェクトの新規作成

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

SQL CLR で使用するPadLeft, PadRightを実装したdll

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

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

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 データベース プロパティ

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

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

データベースに追加されたCLRのアセンブリ

CLR 関数の作成

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

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

CLR 統合の有効化

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

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

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関数を実行してみます。

実行結果

SQL CLR 関数を実行

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

まとめ

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

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

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

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