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

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

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

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

例

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

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

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

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

注
SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージ コードでユーザー定義型、ストアド プロシージャ、関数、集計、またはトリガーを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel (Transact-SQL) ストアド プロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。

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

例

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

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

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

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

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

USE Adventureworks2008R2;
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;

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 構文の 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';

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

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

Currency UDT と ConvertCurrency 関数のコード リストは、CLR (共通言語ランタイム) のサンプルをインストールして確認できます。詳細については、「SQL Server のサンプルとサンプル データベースのインストールに関する注意点」を参照してください。

定義上、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 に登録する必要があります。