SQL Server でのユーザー定義型の登録

[アーティクル]
05/07/2013

Microsoft SQL Server で UDT (ユーザー定義型) を使用するには、その UDT を登録する必要があります。 UDT を登録するには、UDT を使用するデータベースにアセンブリを登録し、型を作成する必要があります。 UDT は、1 つのデータベースにスコープが設定されるので、同一のアセンブリと UDT を各データベースに登録しない限り、複数のデータベースでは使用できません。 UDT アセンブリを登録し、型を作成すると、Transact-SQL やクライアントコードでその UDT を使用できます。詳細については、「CLR ユーザー定義型」を参照してください。

Visual Studio を使用した UDT の配置

UDT を配置する最も簡単な方法は、Microsoft Visual Studio を使用することです。ただし、より複雑な配置シナリオで最も優れた柔軟性を得るためには、このトピックで説明するように Transact-SQL を使用します。

Visual Studio を使用して UDT を作成および配置するには、次の手順を実行します。

[Visual Basic] 言語ノードまたは [Visual C#] 言語ノードに、新しい [データベース] プロジェクトを作成します。
UDT を格納する SQL Server データベースへの参照を追加します。
[ユーザー定義型] クラスを追加します。
コードを記述して UDT を実装します。
[ビルド] メニューの [配置] をクリックします。この結果、SQL Server データベースにアセンブリが登録され、型が作成されます。

Transact-SQL を使用した UDT の配置

Transact-SQL CREATE ASSEMBLY 構文は、UDT を使用するデータベースにアセンブリを登録する場合に使用します。アセンブリは、ファイルシステムに外部的に格納されるのではなく、データベースシステムテーブルに内部的に格納されます。 UDT が外部アセンブリに依存する場合は、それらのアセンブリもデータベースに読み込む必要があります。 CREATE TYPE ステートメントは、UDT を使用するデータベースに UDT を作成する場合に使用します。詳細については、「CREATE ASSEMBLY (Transact-SQL)」および「CREATE TYPE (Transact-SQL)」を参照してください。

CREATE ASSEMBLY の使用

CREATE ASSEMBLY 構文では、UDT を使用するデータベースにアセンブリが登録されます。アセンブリを登録すると、そのアセンブリに依存関係がなくなります。

指定された 1 つのデータベースに複数のバージョンの同じアセンブリを作成することはできません。ただし、特定の 1 つのデータベースに、カルチャに基づいて、複数のバージョンの同じアセンブリを作成することはできます。 SQL Server では、SQL Server のインスタンスに登録されているとおりに、異なる名前で、アセンブリの複数のカルチャバージョンが区別されます。詳細については、.NET Framework SDK の「厳密な名前付きアセンブリの作成と使用」を参照してください。

SAFE 権限セットまたは EXTERNAL_ACCESS 権限セットを指定して CREATE ASSEMBLY を実行すると、アセンブリが検証可能でタイプセーフであることを確認するためのチェックが行われます。権限セットを指定しないと、SAFE が指定されていると想定されます。 UNSAFE 権限セットを指定したコードはチェックされません。アセンブリの権限セットの詳細については、「アセンブリのデザイン」を参照してください。

例

次の Transact-SQL ステートメントでは、AdventureWorks データベースで、SAFE 権限セットを指定して SQL Server に Point アセンブリを登録しています。 WITH PERMISSION_SET 句を省略すると、SAFE 権限セットでアセンブリが登録されます。

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

次の Transact-SQL ステートメントでは、FROM 句に <assembly_bits> 引数を使用してアセンブリを登録しています。この varbinary 値は、ファイルをバイトストリームで表しています。

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78

CREATE TYPE の使用

アセンブリをデータベースに読み込むと、Transact-SQL CREATE TYPE ステートメントを使用して型を作成できます。型を作成すると、そのデータベースで使用できる型のリストに、作成した型が追加されます。型にはデータベーススコープがあり、型はその型を作成したデータベースでしか使用できません。データベース内に UDT が既に存在する場合は、CREATE TYPE ステートメントがエラーで失敗します。

注
CREATE TYPE 構文は、ネイティブな SQL Server 別名データ型を作成する場合にも使用され、別名データ型を作成する手段として sp_addtype に置き換わるものです。 CREATE TYPE 構文の省略可能な一部の引数は CLR の UDT の作成に関係しており、(基本型などの) 別名データ型の作成には適用されません。

詳細については、「CREATE TYPE (Transact-SQL)」を参照してください。

例

次の Transact-SQL ステートメントでは、Point 型を作成します。 EXTERNAL NAME は、AssemblyName.UDTName という 2 部構成の名前付け構文を使用して指定します。

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

データベースからの UDT の削除

DROP TYPE ステートメントを使用すると、現在のデータベースから UDT が削除されます。 UDT を削除すると、DROP ASSEMBLY ステートメントを使用してデータベースからアセンブリを削除できます。

DROP TYPE ステートメントは、次の状況では実行されません。

UDT を使用して定義した列を含むデータベース内のテーブル。
WITH SCHEMABINDING 句を使用してデータベースに作成した UDT の変数またはパラメーターを使用する関数、ストアドプロシージャ、またはトリガー。

例

次の Transact-SQL は、この順序どおりに実行する必要があります。最初に Point UDT を参照するテーブルを削除し、次に型を削除して、最後にアセンブリを削除する必要があります。

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

UDT 依存関係の検出

UDT の列定義を含むテーブルなどの依存オブジェクトがある場合、DROP TYPE ステートメントは失敗します。また、WITH SCHEMABINDING 句を使用してデータベースに作成した関数、ストアドプロシージャ、またはトリガーがあり、これらのルーチンでユーザー定義型の変数やパラメーターが使用される場合にも失敗します。最初にすべての依存オブジェクトを削除してから、DROP TYPE ステートメントを実行する必要があります。

次の Transact-SQL クエリでは、AdventureWorks データベースで、UDT を使用するすべての列とパラメーターを検索しています。

USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
     , c.name AS minor_name, c.type_desc AS minor_type_desc
     , at.assembly_class
  FROM (
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
          FROM sys.columns
     UNION ALL
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
          FROM sys.parameters
     ) AS c
  JOIN sys.objects AS o
    ON o.object_id = c.object_id
  JOIN sys.assembly_types AS at
    ON at.user_type_id = c.user_type_id;

UDT の管理

UDT を SQL Server データベースに作成した後は、型の基になるアセンブリを変更することはできますが、UDT は変更できません。ほとんどの場合、Transact-SQL DROP TYPE ステートメントを使用してデータベースから UDT を削除し、基になるアセンブリに変更を加えて、ALTER ASSEMBLY ステートメントを使用してアセンブリを再読み込みする必要があります。その後、UDT とすべての依存オブジェクトを再作成する必要があります。

例

ALTER ASSEMBLY ステートメントは、UDT アセンブリのソースコードに変更を加え、ソースコードを再コンパイルした後に使用します。 ALTER ASSEMBLY ステートメントを使用すると、サーバーに .dll ファイルがコピーされ、新しいアセンブリに再バインドされます。複雑な構文については、「ALTER ASSEMBLY (Transact-SQL)」を参照してください。

次の Transact-SQL ALTER ASSEMBLY ステートメントでは、ディスク上の指定された場所から Point.dll アセンブリを再読み込みしています。

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'

ALTER ASSEMBLY を使用したソースコードの追加

ALTER ASSEMBLY 構文の ADD FILE 句は、CREATE ASSEMBLY 構文には存在しません。 ADD FILE 句を使用すると、アセンブリに関連付けられるソースコードやその他のファイルを追加できます。ファイルは元の場所からコピーされ、データベース内のシステムテーブルに格納されます。これにより、現在のバージョンの UDT を再作成またはドキュメント化する必要があれば、ソースコードや他のファイルをいつでも使用できます。

次の Transact-SQL ALTER ASSEMBLY ステートメントでは、Point UDT の Point.cs クラスのソースコードを追加しています。 Point.cs ファイルに含まれているテキストがコピーされ、"PointSource" という名前でデータベースに格納されます。

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

アセンブリ情報は、アセンブリがインストールされているデータベース内の sys.assembly_files テーブルに格納されます。 sys.assembly_files テーブルには、次の列があります。

assembly_id
アセンブリに定義される ID。この番号は、同じアセンブリに関連するすべてのオブジェクトに割り当てられます。
name
オブジェクト名。
file_id
各オブジェクトを識別する番号。最初のオブジェクトは、値 1 が割り当てられている assembly_id に関連付けられます。複数のオブジェクトが同じ assembly_id に関連付けられている場合、その後に続く各 file_id 値は 1 ずつ増加します。
content
アセンブリまたはファイルの 16 進数表記。

CAST 関数または CONVERT 関数を使用して、content 列の内容を読み取り可能なテキストに変換できます。次のクエリでは、結果セットを 1 行に制限するために WHERE 句で name を使用して、Point.cs ファイルの内容を読み取り可能なテキストに変換しています。

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

結果をテキストエディターにコピーして貼り付けると、元のファイルに含まれていた改行とスペースが保持されていることがわかります。

UDT とアセンブリの管理

UDT の実装を計画するときは、どのメソッドが UDT アセンブリ自体に必要であり、どのメソッドを独立したアセンブリに作成してユーザー定義関数やストアドプロシージャとして実装する必要があるかを検討します。メソッドを個別のアセンブリに分離すると、テーブルの UDT 列に格納されるデータに影響を与えずに、コードを更新できます。新しい定義で UDT 列の以前の値を読み取ることができ、型の署名が変更されない場合にのみ、UDT 列や他の依存オブジェクトを削除しないで UDT アセンブリを変更できます。

UDT の実装に必要なコードから、変更される可能性がある手続き型コードを分離すると、メンテナンスが大幅に簡素化されます。 UDT が機能するのに必要なコードのみを含め、UDT の定義をできる限り単純にしておくと、コードリビジョンやバグ修正のために UDT 自体をデータベースから削除する必要が生じるリスクが軽減されます。

Currency UDT と通貨換算関数

AdventureWorks サンプルデータベースの Currency UDT には、UDT とその UDT に関連付けられた関数を構造化するための推奨方法の役立つ例が用意されています。 Currency UDT は、特定のカルチャの通貨システムに基づいて金額を処理するために使用します。また、ドルやユーロなど、さまざまな通貨の種類を保存できます。 UDT クラスでは、カルチャ名を文字列として、金額を decimal データ型として公開します。必要なシリアル化メソッドは、クラスを定義するアセンブリ内にすべて含まれます。あるカルチャから別のカルチャへの通貨換算を実装する関数は、ConvertCurrency という外部関数として実装されます。また、この関数は独立したアセンブリに存在します。 ConvertCurrency 関数は、AdventureWorks データベース内のテーブルから換算率を取得することで機能します。換算率の基になるレートが絶えず変化する場合や既存のコードにその他の変更が加えられる場合は、Currency UDT に影響を与えずに、容易にアセンブリを変更できます。

Currency UDT と ConvertCurrency 関数のコードリストは、CLR (共通言語ランタイム) のサンプルをインストールして確認できます。

複数のデータベース間での UDT の使用

定義上、UDT のスコープは 1 つのデータベースに設定されています。このため、あるデータベースで定義されている UDT を別のデータベースの列定義に使用することはできません。 UDT を複数のデータベースで使用するには、各データベースで同一のアセンブリに対して CREATE ASSEMBLY ステートメントと CREATE TYPE ステートメントを実行する必要があります。アセンブリの名前、厳密な名前、カルチャ、バージョン、権限セット、およびバイナリの内容が同じ場合、それらのアセンブリは同一であると見なされます。

両方のデータベースに UDT を登録し、UDT にアクセスできるようになると、あるデータベースの UDT 値を別のデータベースで使用するために変換できます。次のシナリオでは、同一の UDT を複数のデータベース間で使用できます。

異なるデータベースで定義されているストアドプロシージャの呼び出し。
異なるデータベースで定義されているテーブルのクエリ。
あるデータベーステーブルの UDT 列から UDT データを選択し、その UDT データを同一の UDT 列を使用して 2 つ目のデータベースに挿入する場合。

このような状況では、必要なすべての変換がサーバーで自動的に行われます。 Transact-SQL CAST 関数または CONVERT 関数を使用して、明示的に変換を実行することはできません。

SQL Server データベースエンジンが tempdb システムデータベースに作業テーブルを作成するときは、UDT を使用するための操作が不要であることに注意してください。 UDT を含み、ユーザーに意識させることなく tempdb を使用するカーソル、テーブル変数、およびユーザー定義のテーブル値関数の処理がこれに該当します。ただし、UDT 列を定義する tempdb に一時テーブルを明示的に作成する場合は、ユーザーデータベースの場合と同様に UDT を tempdb に登録する必要があります。

次の方法で共有

SQL Server でのユーザー定義型の登録

Visual Studio を使用した UDT の配置

Transact-SQL を使用した UDT の配置

CREATE ASSEMBLY の使用

例

CREATE TYPE の使用

例

データベースからの UDT の削除

例

UDT 依存関係の検出

UDT の管理

例

ALTER ASSEMBLY を使用したソースコードの追加

UDT とアセンブリの管理

Currency UDT と通貨換算関数

複数のデータベース間での UDT の使用

関連項目

概念

その他のリソース

次の方法で共有

SQL Server でのユーザー定義型の登録

Visual Studio を使用した UDT の配置

Transact-SQL を使用した UDT の配置

CREATE ASSEMBLY の使用

例

CREATE TYPE の使用

例

データベースからの UDT の削除

例

UDT 依存関係の検出

UDT の管理

例

ALTER ASSEMBLY を使用したソース コードの追加

UDT とアセンブリの管理

Currency UDT と通貨換算関数

複数のデータベース間での UDT の使用

関連項目

概念

その他のリソース

ALTER ASSEMBLY を使用したソースコードの追加