特集 SQL Server 2005の新機能「SQL CLR」（後編） Visual Studio 2005でSQL CLRを実装してみる 中 博俊（Microsoft MVP 2005 - Visual C#） 2005/04/09

Page1 Page2 Page3 Page4

　マイクロソフトが提供するデータベース・サーバ「Microsoft SQL Server」（以降、SQL Server）の次期バージョンであるSQL Server 2005（開発コード名：Yukon）では、.NET Frameworkの共通言語ランタイム（CLR：Common Language Runtime）がデータベースに統合された「SQL CLR」という新機能が提供される。これにより、SQL Serverは.NETプログラムと緊密に連携できるようになる。

　前編では、SQL CLRの機能概要について解説した。今回はSQL CLRの機能を利用するとどのようなプログラミングが可能になるのか具体的に説明していこう。

　なお本稿では、すべてVisual Studio 2005 ベータ1（.NET Frameworkのバージョンは2.0.3600.0）とSQL Server 2005ベータ2を利用している。

1. SQL CLRプログラミングの種類

　まずはSQL CLRにより実装可能なSQLプログラムの種類から見ていく。

　従来のTransact-SQLによるSQLプログラミングにおいては、

• スカラ値ユーザー定義関数
• テーブル値ユーザー定義関数

という2種類の関数と、

• ユーザー定義プロシージャ
• ユーザー定義トリガ

という2種類のストアド・プロシージャの、合計4種類のSQLプログラムが実装できた。

　新しいSQL CLRにおいては、これらに加えて、

• ユーザー定義型

というデータ型の実装が可能になり、全部で5種類のSQLプログラムが利用できるようになる。これらをC#やVB.NETを利用して記述可能だ。

　それぞれのSQLプログラムがどのようなものかを簡単に見てみておこう。

（1）スカラ値ユーザー定義関数

　戻り値として1つの値（＝スカラ値：整数や実数、文字型などの単一データ）を返す関数。単純な数値計算関数や文字列処理用の関数、「SELECT COUNT(*)」（レコード数を取得するSQL文）の結果を返す関数などを記述するために利用される。

　後ほどスカラ値ユーザー定義関数の具体的なサンプルを作成する。

（2）テーブル値ユーザー定義関数

　戻り値としてテーブル値を返す関数。この関数はテーブル形式のデータをそのまま返せるので、その結果データ（＝戻り値）をSELECT文のFROM句に直接指定することもできる。これを利用すれば、ほかのテーブルと連結したデータを取得したり、WHERE句による絞り込みに利用したりできるなど、SELECT文によるクエリと親和性が高い。

　この関数は、SQL CLRを利用する場合でも、次のユーザー定義プロシージャに次いで利用局面が多いだろう。

（3）ユーザー定義プロシージャ

　一般的に「ストアド・プロシージャ」と呼ばれるものは、このユーザー定義プロシージャである。ユーザー定義プロシージャは、その戻り値をクエリの中で直接使用できないのと、必ずしも戻り値を返すとは限らないという特徴を除くと、その用途はテーブル値ユーザー定義関数とほぼ同じである。ADO.NETなどから簡単に呼び出せるので、.NETプログラムとの親和性が高い。

（4）ユーザー定義トリガ

　ユーザー定義トリガ（以降、トリガ）とは特殊なストアド・プロシージャのことで、これを利用すれば、データがどのように変更されたかを特殊なテーブルで取得できるので、そのタイミングに合わせてSQL処理を行うことができる。

　トリガの機能の利用には注意が必要だ。例えばINSERT文を実行する側からはトリガの存在が見えないため、連鎖的に次々と更新を行うようなSQL処理を行うと、予期しない動作を引き起こしかねない。そのため、トリガの積極的な利用はあまりお勧めできない。

　ただし、テーブルへの不正な更新の履歴を取るなどといった使い方の場合には有用だ。例えば、同じテーブル・レイアウトで主キーなどのキー情報を設定しないテーブルを作成しておき、トリガを使って、DELETE文が実行されるタイミングで変更（DELETE＝削除）前のデータをそのテーブルへ退避させておき、もしもの場合に後でそのデータを復元する。このような用途では、トリガは利用しやすい機能である。

（5）ユーザー定義型

　SQL Server 2005以前のユーザー定義型では、既存のデータ型の別名を宣言する程度の定義しかできなかった。例えば、「sysname」は「nvarchar(128)」を意味する、といった定義などである。

　これがSQL CLRになると大幅に様相は変わる。オリジナルの型をクラスとして定義し、それらを管理できるようになるのだ。例えば、画像の名前、サイズ、撮影日などを持った「画像情報」型を定義して、次のようなクエリを発行できるようになる。

SELECT * From picture_data WHERE 画像情報.画像の名前 LIKE ‘%大阪港%’

　このクエリでは、画像の名前に「大阪港」が含まれているレコードをすべて取得できる。WHERE句に指定している「画像情報.画像の名前」は、独自に定義した型のメンバである。

　テーブルのリレーションを用いても同じようなことが実現できるが、テーブルは1つのカラム（＝列）の中に複数のデータ型をまとめて格納することができない。このため、どうしても複数のテーブルを連結して1つのデータとして使うという形になる。しかしユーザー定義型は、クラスのインスタンスがテーブルのカラムにそのまま格納されているようなもので、それを利用する際にはピリオドを使ってその型のメンバ・フィールドにアクセスできる（例えば上のクエリ例では「画像情報」型のメンバ・フィールド「画像の名前」に対してLIKE演算子によるパターン検索を行っている）。ユーザー定義型は、上のクエリ例の「画像データとそれに付随する情報（撮影時間や撮影者など）」のように、データをセットで保存できるので、データをひとまとめにして取り扱いたい場合に便利だ。

　なお、2007年ごろに登場するといわれているWinFS（＝Windows OSの新しいファイル・システム）は、このユーザー定義型を最大限活用する。ユーザー定義型の詳細については今回は割愛するが、注目しておくべき機能だと筆者は考えている。

　SQL CLRでは、以上のように、それぞれ特徴のある5種類のプログラム形態が実装できる。本稿では、これらのプログラム形態のうち、スカラ値ユーザー定義関数を用いて簡単なHello World!プログラムを作成してみるが、その前にSQL CLRで利用できるデータ型について知っておいた方がよい。そこで次に.NETにおける型とSQL CLRでの型の違いについて説明しよう。

2. .NETにおける型とSQL CLRでの型の違い

　.NETでは、最初からSQL Serverプログラミング向けに、System.Data.SqlTypes名前空間で定義されたデータ型（以降、SQL Serverネイティブ互換型）が用意されている。.NET Framework 2.0では、これに若干の拡張が行われているが、基本的には変わらない。

　例えば、SQL Serverにおけるデータ型（以降、SQL Serverデータ型）にはUnicode文字列を格納するためのNvarcharというデータ型があるが、.NETではこれに対応するSQL Serverネイティブ互換型としてSqlString型が用意されている。そして、これは.NETの基本データ型（以降、.NETネイティブ型）ではString型に対応する。

　このように、同じデータを表現する場合でも、以下の3つの種類のデータ型が存在しており、これらは相互に対応している。

• SQL Server側のデータ型である「SQL Serverデータ型」
• .NET側のSQL Server用のデータ型である「SQL Serverネイティブ互換型」
• .NET Framework（CLR）上でのデータ型である「.NETネイティブ型」

　これらの対応関係を把握しているとプログラムしやすくなる。これらの対応を以下の表にまとめたので確認してほしい。なお、「SqlDbType列挙体（System.Data名前空間）」はADO.NETのSqlCommand.ParametersオブジェクトのAddメソッドなどでデータ型を指定したりするための値だ。

SQL Serverデータ型	SqlDbType列挙体	SQL Serverネイティブ互換型	.NETネイティブ型
Varbinary	VarBinary	SqlBytes、SqlBinary	Byte[]
Binary	Binary	SqlBytes、SqlBinary	Byte[]
Image	Image	SqlBinary	None
Varchar	VarChar	SqlString	None
Char	Char	SqlString	None
Nvarchar	NVarChar	SqlChars、SqlString	String、Char[]
Nchar	NChar	SqlChars、SqlString	String、Char[]
Text	Text	SqlString	None
Ntext	NText	SqlString	None
Uniqueidentifier	UniqueId	SqlGuid	Guid
Bit	Bit	SQLBoolean	Boolean
Tinyint	TinyInt	SqlByte	Byte
Smallint	SmallInt	SqlInt16	Int16
Int	Int	SqlInt32	Int32
Bigint	BigInt	SqlInt64	Int64
Smallmoney	SmallMoney	SqlMoney	Decimal
Money	Money	SqlMoney	Decimal
Numeric	Numeric	SqlDecimal	Decimal
Decimal	Decimal	SqlDecimal	Decimal
Real	Real	SqlSingle	Single
Float	Float	SqlDouble	Double
Smalldatetime	SmallDateTime	SqlDateTime	DateTime
Datetime	DateTime	SqlDateTime	DateTime
SQL_variant	Variant	Object	Object
Timestamp	TimeStamp	SqlBinary	None
Xml	Xml	SqlXml	None
SQL Serverおよび.NETにおけるデータ型の対応表
この表は.NET Framework 1.1向けのMSDNライブラリや、SQL Server 2005ベータ2の資料を基に起こした。参考にしたサイトは、MSDNの「SqlDbType列挙体」と「System.Data.SqlTypes名前空間」だ。この表の.NETネイティブ型でNoneとなっているものは、直接対応するデータ型が存在しないものだ。例えばSQL Serverデータ型の「Char」は、Shift-JISなどのUnicodeではない文字のデータ型であるが、.NETネイティブ型ではすべてUnicode文字で処理が行われるため、.NETネイティブ型には直接的には対応していない。しかし、これはほかのデータ型で代替することも可能だ。例えば先ほどのSQL Serverデータ型の「Char」は、.NETネイティブ型「String」で代替することも可能である。

　SQL Server用のプログラムを作成する場合には、.NETプログラム内で使用するデータ型としては当然ながら、.NETネイティブ型よりもSQL Serverネイティブ互換型の方が実行効率がよい。これはSQL CLRでも同様なので、SQL CLRプログラミングでもSQL Serverネイティブ互換型を利用することをお勧めする。現在の.NET Framework 1.xでは利用例が少ないようだが、SQL Serverネイティブ互換型をぜひ調べてみてほしい。

　さてそれでは実際にSQL CLRのプログラムを作成してみよう。

INDEX
	［特集］SQL Server 2005の新機能「SQL CLR」（前編）
	SQL Serverプログラミングを革新するSQL CLRとは？
	1．従来のSQLプログラミングについて
	2．SQLプログラミングを革新するSQL CLRとは？
	3．SQL CLRの特長とストアド・プロシージャとの使い分け
	［特集］SQL Server 2005の新機能「SQL CLR」（後編）
	Visual Studio 2005でSQL CLRを実装してみる
	1．SQL CLRプログラミングの種類
	2．SQL CLRでHello World!を作成してみる
	3．Visual StudioによるSQL CLRの開発
	4．SQL CLRプログラムの実行

　

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）