SQL-Serverは、SQL-Server 2000の時代から関数を作る事が出来るのですが、それは、あくまでもSQL(Microsoft Transact SQL)の範囲内でのプログラムになってしまいます。
本来ならば、データベースを操作するための言語であるSQLですから、通常のプログラミング言語と同等の処理を組み上げるのは独特の苦労がありますし、一行ごとに値を返すスカラ型関数を使う場合は、パフォーマンスの厳しい低下も想定しなければなりません。
こうなったら、関数をC#言語で作れたら良いのになぁって、やっぱり思いますよね。
それをズバリ実現したのが、SQL CLRと呼ばれる仕組みです。
しかも、SQL CLRは、高価な製品版のVisual Studio 2008を買う必要はなく、フリーのVisual C# 2008 Expressでバッチリ作る事が出来てしまうのが嬉しいところ。
ただ、SQL CLRには、非常に厳しいセキュリティの壁が立ちはだかっています。
C#言語でプログラムが作れるということは、何でも出来てしまうわけで、そもそもSQL-Server 2008 Expressは、インストールしただけではSQL CLRの機能が封印されています。
SQL-Server 2008 Expressのポリシー管理などで、封印されているSQL CLRを使えるように設定するのが第一歩です。
とりあえず、これで、一番厳しいランクのセキュリティである「SAFE」で動く関数だけは記述できるようになります。「SAFE」以上の権限である「EXTERNAL ACCESS」と「UNSAFE」は、今回は取り上げず、また後日の記事で書かせて頂きたいと思います。
SQL CLRを使えるようになったら、以下の手順でSQL関数を作る事が出来ます。
今回は、サンプルプログラムとして、一行のCSVデータから、指定した順番(0スタート)の要素を抽出するという関数を作ってみました。
こういう処理は、確かに通常のSQL関数でも作れますが、コードが非常に複雑になる上にパフォーマンスが厳しいものになりがちです。しかし、SQL CLRならばその心配がほぼ解消されております。
さて、作成・登録の手順ですね。
(1)Visual C# 2008 Expressのプロジェクトは、「クラスライブラリ」を指定。
SQL関数の文法に沿ってプログラムを記述します。これがSQL CLRとなります。
(2)作ったプログラムをビルドすると、DLLが出来上がります。
このDLLを、SQL-Server 2008 Express側で「CREATE ASSEMBLY文」でもって登録。
これを「アセンブリを登録する」と言います。DLLはアセンブリと呼ばれるのがポイント。
(3)SQL-Server 2008側で、SQL CLRと一対一で対応した、インターフェースだけのSQL関数を記述して、両者を紐付けます。
(4)実際の使用は、(3)でSQL CLRと紐付けたSQL関数を使います。
しかし、中身はC#言語ですから、パフォーマンスは桁違いに高まる可能性があります。
詳しくは、実際のプログラムコードのスクリーンショットをご覧いただきたいのですが、C#言語側には、SQL CLRならではの部位が色々と出てきます。
大きなポイントは、関数の前に[Microsoft.SqlServer.S
erver.SqlFunction]を宣言している点です。忘れやすいのですが、これが無いと、SQL-Serverは、関数が見つからないと言ってきますので注意が必要です。
型に関しては、Sqlxxx型を使っていますが、これはSQL-Serverとのやりとりでパフォーマンスがアップするので使っていますが、通常の型でもエラーにはなりません。
注意が必要なのは、SQL-Server側で対応する型です。
文字列は、必ずUnicode型でなければエラーになってしまいます。C#言語側のSqlString型やstring型に対応するのは、SQL-Server側ではnvarchar型であり、nの付かないvarchar型などでは、エラーになってしまいます。この点に気が付かないと、正しいはずなのにエラーが消えないという苦しい展開になってしまいますので注意が必要です。
また、登録の解除ですが、アセンブリ(DLL)の削除をするには、登録した全ての関数を削除してからじゃないと駄目です。1つのアセンブリ(DLL)に沢山の関数を抱えていると、再登録が大変かも知れませんが、そこはうまくスクリプト
を組んでおくなどすれば、大丈夫だと思われます。登録はちょっと面倒ですが、こればっかりは何回か作業をしてみて慣れるしかありません。
しかし、その面倒を遥かに超えるメリットが、SQL CLRには存在します。
とにかく、SQL-Server単体では絶対に不可能だった機能が作れるようになるし、既存の関数をSQL CLRで作り変えるだけで、劇的なパフォーマンスアップをする可能性もあります。
今回のサンプルコードではありませんが、実際に私が普通のSQL-Server関数と、SQL CLRで作った同等機能の関数を使った時、30万件程度のデータに対するスカラ型関数の使用において、12倍もの速度差が付いたものがあります。
また、Visual C# 2008のソリューション管理を使って、DLLをデバッグするためのプロジェクトを作っておく事も可能です。
Visual C# 2008 Expressによるデバッグが可能というのは、生産性という面からも絶大なメリットがあります。
このSQL CLRは、大げさでも何でもなく、まさにSQL-Server 2008 Expressの必殺技と言っても過言ではない、極めて極めて大きな機能です。
SQL-Server 2008 Expressをやるのならば、絶対に覚えておいて損は無いはずです。
私もまだまだ勉強中ですが、頑張りたい機能です。
