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

  • [アーティクル]

適用対象: SQL サーバー

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

Visual Studio を使用した UDT の配置

UDT をデプロイする最も簡単な方法は、Microsoft Visual Studio を使用することです。 ただし、より複雑な展開シナリオと柔軟性を最大限に高める場合は、このトピックで後述するように Transact-SQL を使用します。

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

  1. Visual Basic または Visual C# 言語ノードで、新しい Database プロジェクトを作成します。

  2. UDT を含む SQL Server データベースへの参照を追加します。

  3. ユーザー定義型クラスを追加します。

  4. コードを記述して UDT を実装します。

  5. Build メニューから Deploy を選択します。 これにより、アセンブリが登録され、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 データベース内の SQL Server の Point アセンブリを SAFE 権限セットで登録します。 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 ステートメントがエラーで失敗します。

Note

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

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

例

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

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 データベースに作成した後は変更できませんが、型の基になっているアセンブリを変更することはできます。 ほとんどの場合、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
指定した assembly_id に関連付けられた最初のオブジェクトが 1 の値を指定した、各オブジェクトを識別する数値。 同じ 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 とそれに関連する関数を構成するための推奨される方法の便利な例が用意されています。 Currency UDT は、特定のカルチャの通貨システムに基づいてお金を処理するために使用され、ドル、ユーロなど、さまざまな通貨の種類を格納できます。 UDT クラスは、カルチャ名を文字列として公開し、金額を decimal データ型として公開します。 必要なシリアル化メソッドは、クラスを定義するアセンブリ内にすべて含まれます。 カルチャ間の通貨換算を実装する関数は、 ConvertCurrency という名前の外部関数として実装され、この関数は別のアセンブリに配置されます。 ConvertCurrency 関数は、AdventureWorks データベース内のテーブルから変換率を取得することによって、その処理を行います。 変換率のソースを変更する必要がある場合、または既存のコードに対して他の変更が必要な場合は、 Currency UDT に影響を与えずにアセンブリを簡単に変更できます。

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

複数のデータベース間での 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 に登録する必要があります。

参照

CLR ユーザー定義型