SQL CLRの仕組みとメリットを理解しよう

SQL Server 2005では、.NET FrameworkのCLR（Common Language Runtime：共通言語ランライム）コンポーネントが統合されました。CLRは.NETをサポートする各言語共通の実行エンジンですが、SQL Server 2005上でもCLRによるマネージドコードの実行が可能となりました（CLR上で実行されるコードをマネージドコードと呼びます）。

　SQL Server 2005上で利用可能となったこの新しい技術を「SQL CLR」と呼び、マイクロソフトでは開発に関する新機能の中でも大きく取り上げています。

　前回までお送りしたIntegration Servicesに続き、今回から複数回にわたってアプリケーション開発者にとって特に重要となるSQL CLRを解説していきます。

SQL CLRの登場背景

　これまで、データベースのデータを処理する手法は、大きく分けて2つ存在しました。1つ目がADO.NETなどを利用した外部プログラムからのアクセス、2つ目がTransact-SQLを利用したストアドプロシージャからのアクセスです。

外部プログラムからのアクセス

　現在、データベースを利用する際に最も一般的な手法が外部プログラムからのアクセスです。ADO.NETなどのデータベースへアクセスするインターフェイスを利用し、参照や更新などの操作を行います。

　最も一般的、ということでVisual Studioなどの開発環境やライブラリなども整っており、C#やVisual Basic .NETなど使い慣れた各種言語でデータベースへのアクセスを行うプログラムを記述できるため、開発生産性が非常に高い手法となります。

　ただし、一点問題があります。SQL Serverの外部からアクセスを行うため、どうしてもパフォーマンスに問題が発生してしまいます。クライアントPCからアクセスした場合はネットワークがボトルネックとなり、SQL Serverと同一のサーバ上で実行したとしてもプロセスを超えたアクセスとなるため、処理効率の低下が避けられません。

図1　クライアントからのアクセスによるボトルネック

Transact-SQLを利用したストアドプロシージャ

　ストアドプロシージャとは、SQLを拡張したプログラム言語Transact-SQLを利用して、一連のSQLの処理手続きを記述したプログラムです。あらかじめデータベースへの処理を1つのプログラムにまとめRDBMSに保存しておき、呼び出せるようにしたものです。

　ストアドプロシージャはSQL Serverのプロセス内で実行されるため、非常に高速な処理が可能となります。このため、大量のデータを操作する際などは、非常に優れた手法といえます。

　ただし、Transact-SQLは、データベース操作のための専用言語ということで、データベース操作以外の処理実装が困難であり、言語としての仕様の限界、開発環境の問題などから、慣れたプログラマでなければ生産性の維持が難しい言語です。

　以上のように、それぞれの手法には一長一短があり、ここにSQL CLRが登場した要因が存在します。

 

SQL CLRのメリット

　SQL CLRはSQL ServerにCLRが統合されることによって誕生しました。CLRとは、Javaの仮想マシンのようにプラットフォーム間の違いを吸収し、さらにさまざまなクラスライブラリなどのサービスを提供する、.NETの根幹をなすフレームワークです。.NET Frameworkの登場以来、CLRを中心とした開発環境が着々と整いつつありますが、ストアドプロシージャの開発でもこれらの技術を利用可能とするために生まれたのがSQL CLRです。

　SQL CLRは従来の方式に比べてどういった点が優れているのか、具体的な利点を見てみましょう。

慣れ親しんだ言語でのデータベース・プログラミング

　SQL CLRでは、.NETをサポートするC#やVisual Basic .NETといった言語でストアドプロシージャやユーザー定義関数、トリガといったデータベース・プログラムを記述できます。これまでデータベース・プログラムを開発するにはTransact-SQLを利用する必要がありましたが、SQL CLRの登場により慣れ親しんだ言語での開発が可能となります。また、重要な点として、SQL CLRで作成したストアドプロシージャやユーザー定義関数を利用する際、クライアントはSQL CLRで作成されたことを意識する必要はなく、Transact-SQLで作成されたプログラムと同様の呼び出しを行うことができます。

開発生産性の向上

　開発者は.NET Frameworkで提供される膨大なクラスライブラリを利用することにより、ファイル操作や正規表現、バイナリ操作などこれまで困難だった数多くの処理を容易に実装できます。.NET Frameworkで提供されるクラスライブラリだけでなく、独自開発のライブラリ群の利用も容易に行えるため、コード資産の有効活用という点でも優れています。

　また、統合開発環境であるVisual Studio 2005では、コードの記述からデバッグ、SQL Serverへの配置までSQL CLRを完全にサポートしています。Visual Studio 2005を利用してSQL CLRの開発を行うことにより、デバッグやコーディング支援、プロジェクト単位での管理、コードの履歴管理など、生産性を向上させる各種機能の利用が可能となります。

パフォーマンスの向上

　SQL CLRはSQL Serverと同一プロセス上で実行されます。このため、外部プログラムからのアクセスに比べ、非常に高速な処理が可能となります。従来はパフォーマンスの向上を目指す場合、Transact-SQLを利用する必要がありましたが、SQL CLRにより、C#やVisual Basic .NETを利用してパフォーマンスの高いプログラムを作成できます。

　以上のように、SQL CLRは多くの面で優れた機能が提供されており、従来型の手法に比べ、開発生産性とパフォーマンスの両立を実現しています。また、C#やVisual Basic .NETなどの言語を利用した開発を行うことが可能となり、クライアントからサーバまで一貫した思想に基づいた開発を行えるため、プログラミング・モデルの一貫性を向上させることができます。プログラミング・モデルの一貫性は、コーディングだけでなく設計やテスト、運用など開発にかかわるさまざまなフェイズで生産性向上に寄与するでしょう。

SQL ServerへのSQL CLRの登録

　SQL CLRは.NETをサポートする各言語でコンパイルされたDLLファイル（アセンブリ）をSQL Serverへ登録することによって動作します。必要な作業としては、コンパイルによるDLLファイルの作成、DLLファイルの登録、登録したDLLファイルの呼び出し方法の登録、という3つの作業になります。

 

図2　SQL ServerへのSQL CLRの登録

 

SQL CLRを利用するためには、コンパイルに加えて、DLLファイルの登録と呼び出し方法の登録が必要となる。

 

　SQL Serverへの登録により、Transact-SQLで作成したストアドプロシージャと同様に処理の呼び出しが可能となります。また、Visual Studio 2005では、SQL CLRを完全にサポートしており、コーディングからSQL Serverへの登録まで一連の作業をVisual Studioより行うことができます。

SQL CLRのセキュリティモデル

　SQL CLRではネットワークやレジストリ、ファイルなど多様なリソースへのアクセスが可能となりますが、無制限にアクセスを認めているわけではありません。SQL CLRではデータベースへ登録するアセンブリに対し「権限セット」を指定することが可能です。権限セットは

• SAFE
• EXTERNAL-ACCESS
• UNSAFE

の3つから選択可能で、リソースへのアクセスを制限できます。

許可レベル	説明
SAFE	最も制限されたアクセス許可。ファイル、ネットワーク、レジストリなど外部リソースへのアクセスはできない
EXTERNAL-ACCESS	SAFEで許可された範囲に加え、ファイルやディレクトリへの読み取り／書き込み、ネットワーク、イベントログ、レジストリなどへのアクセスを認める
UNSAFE	EXTERNAL-ACCESSに加え、SQL CLRからアンマネージドコード（C++のコードなど）の呼び出しを認める
表2　SQL CLRのセキュリティモデル

 

 

 

　デフォルトではSAFEとなっており、多くのSQL CLRがSAFEでの利用となるはずです。外部へのアクセスを認める場合にはEXTERNAL-ACCESS設定を利用します。また危険を伴いますがWin32APIの呼び出しなどを必要とする場合はUNSAFE設定とします。

 

Transact-SQL、ADO.NETとSQL CLRの比較

　SQL CLRの基本情報が分かったところで、Transact-SQLとADO.NET、SQL CLRのコード比較を行ってみましょう。サンプルとして、それぞれ1行のデータをテーブルへ追加しています。

CREATE PROCEDURE [dbo].[SampleProc]
(
@id int,
@name nvarchar(50)
)
AS
INSERT INTO [Table_1]
([id],[name])
VALUES
(@id,@name)

リスト1　Transact-SQLによるデータ挿入処理
引数に従いデータを追加するストアドプロシージャ。引数を埋め込んだINSERT文を発行している。

 

cmd.CommandText =
"INSERT INTO [Table_1] ([id],[name]) VALUES(@id,@name)";
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@name", SqlDbType.NVarChar,50);
cmd.Parameters["@id"].Value = 1;
cmd.Parameters["@name"].Value = "atmark it";
cmd.ExecuteNonQuery();

リスト2　ADO.NETによるデータ挿入処理（C#）
INSERT文中に埋め込んだパラメータ（@id,@name）に対し値をセットし、コマンドを発行している（データベースとの接続部などは省略）。

 

SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection("Context Connection=true");
cmd.Connection.Open();
cmd.CommandText =
"INSERT INTO [Table_1] ([id],[name]) VALUES (@id,@name)";
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100);
cmd.Parameters["@id"].Value = "1";
cmd.Parameters["@name"].Value = "atmark it";
cmd.ExecuteNonQuery();

リスト3　SQL CLRによるデータの挿入処理（C#）
ADO.NETのコードとほぼ変わらない内容となっている。

 

 

 

　まず、Transact-SQLのコード（リスト1）ですが、SQL発行に特化した言語体系のため、サンプルのような単純な処理であれば、非常に短いコードで済みます。このことから、既存のTransact-SQLをすべてSQL CLRに置き換えるべきではない、ということが分かります。単純なデータアクセスであれば、Transact-SQLの優位性は変わりません。

　続いて、ADO.NET（リスト2）とSQL CLR（リスト3）の比較ですが、どちらもC#で書かれているため、構文などに変化はありません。変わった部分として、データベースへの接続方法が異なります。ADO.NETでは接続先のデータベースを

server=MyServer; database=AdventureWorks……

 

 

 

というような形で指定します。

　一方、SQL CLRの場合、実行ユーザーがSQL CLRを実行するSQL Serverに接続しているため、この情報を受け継いで接続先情報とすることができます。このような接続を「コンテキスト接続」と呼びます。コンテキスト接続はネットワークやプロセスを超えず、インプロセスのみの接続なので、パフォーマンスの向上が見込めます。

　コンテキスト接続を利用せず、従来のADO.NETのように接続先を指定し別のSQL Serverへ接続することも可能です。また別のデータソースに接続することもできます。例えば、System.Data.OracleClientプロバイダを使用すればSQL Server内部から直接Oracleサーバに接続することも可能です。

まとめ

　今回はSQL CLRの基本情報の紹介と、従来の手法に対する利点などの確認を行いました。SQL CLRが革新的な技術であり、今後SQL Server 2005を利用するうえで大きなインパクトとなることを感じ取れたと思います。

　これまで、SQL Serverを利用したアプリケーションでパフォーマンスが求められる場合、Transact-SQLを利用する必要がありましたが、生産性の問題などから、苦労した開発者も多いはずです。SQL CLRがTransact-SQLのすべてを置き換えられるわけではありませんが、多くの問題に直面する開発者には救いの手となるはずです。

　次回は、Visual Studio 2005を利用し、より具体的なSQL CLRの紹介を行います。お楽しみに！

 

手法	メリット	デメリット
外部プログラム	開発生産性が高い	パフォーマンスが低い
Transact-SQLによる ストアドプロシージャ	パフォーマンスが高い	開発生産性が低い
表1　アクセス手法によるメリット／デメリット パフォーマンスと開発生産性の両立が、SQL CLRの登場背景となった。