SQL Server でユーザー定義型を登録する
適用対象:
SQL Server
SQL Server でユーザー定義型 (UDT) を使用するには、それを登録する必要があります。 UDT を登録するには、UDT を使用するデータベースにアセンブリを登録し、型を作成する必要があります。 UDT のスコープは 1 つのデータベースであり、同一のアセンブリと UDT が各データベースに登録されていない限り、複数のデータベースでは使用できません。 UDT アセンブリが登録され、型が作成されたら、Transact-SQL およびクライアント コードで UDT を使用できます。 詳細については、CLR ユーザー定義型 を参照してください。
Visual Studio を使用して UDT をデプロイする
UDT をデプロイする最も簡単な方法は、Visual Studio を使用する方法です。 ただし、より複雑なデプロイ シナリオと最大の柔軟性については、この記事で後述するように Transact-SQL を使用してください。
Visual Studio を使用して UDT を作成および配置するには、次の手順を実行します。
Visual Basic または Visual C# 言語ノードで、新しい Database プロジェクトを作成します。
UDT を含む SQL Server データベースへの参照を追加します。
ユーザー定義型クラスを追加します。
コードを記述して UDT を実装します。
Build メニューから Deploy を選択します。 これにより、アセンブリが登録され、SQL Server データベースに型が作成されます。
Transact-SQL を使用して UDT をデプロイする
Transact-SQL CREATE ASSEMBLY 構文は、UDT を使用するデータベースにアセンブリを登録するために使用されます。 ファイル システムの外部ではなく、データベース システム テーブルに内部的に格納されます。 UDT が外部アセンブリに依存する場合は、それらのアセンブリもデータベースに読み込む必要があります。
CREATE TYPE ステートメントは、使用するデータベースに UDT を作成するために使用されます。 詳細については、「CREATE ASSEMBLY と CREATE TYPE
アセンブリの作成を使用する
CREATE ASSEMBLY 構文は、UDT を使用するデータベースにアセンブリを登録します。 アセンブリを登録すると、そのアセンブリに依存関係がなくなります。
特定のデータベースに同じアセンブリの複数のバージョンを作成することはできません。 ただし、特定のデータベースのカルチャに基づいて、同じアセンブリの複数のバージョンを作成できます。 SQL Server では、アセンブリの複数のカルチャ バージョンが、SQL Server のインスタンスに登録されている異なる名前で区別されます。 詳細については、「厳密な名前付きアセンブリを作成して使用する」を参照してください。
SAFE または EXTERNAL_ACCESS 権限セットを使用して CREATE ASSEMBLY が実行されると、アセンブリが検証可能でタイプ セーフであることを確認するためにチェックされます。 権限セットの指定を省略すると、SAFE が想定されます。
UNSAFE アクセス許可セットを持つコードはチェックされません。 アセンブリ権限セットの詳細については、「アセンブリの設計」を参照してください。
例
次の Transact-SQL ステートメントは、AdventureWorks2022 データベースの SQL Server の Point アセンブリを、SAFE 権限セットに登録します。
WITH PERMISSION_SET 句を省略すると、アセンブリは SAFE 権限セットに登録されます。
USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll'
WITH PERMISSION_SET = SAFE;
次の Transact-SQL ステートメントは、FROM 句の引数 <assembly_bits> 使用してアセンブリを登録します。 この varbinary 値は、ファイルをバイト ストリームとして表します。
USE AdventureWorks2022;
CREATE ASSEMBLY Point
FROM 0xfeac4 ... 21ac78
create 型を使用する
アセンブリがデータベースに読み込まれたら、Transact-SQL CREATE TYPE ステートメントを使用して型を作成できます。 型を作成すると、そのデータベースで使用できる型のリストに、作成した型が追加されます。 型にはデータベース スコープがあり、型はその型を作成したデータベースでしか使用できません。 UDT がデータベースに既に存在する場合、CREATE TYPE ステートメントはエラーで失敗します。
Note
CREATE TYPE 構文は、ネイティブ SQL Server エイリアス データ型の作成にも使用され、エイリアス データ型を作成する手段として sp_addtype を置き換えることを目的としています。
CREATE TYPE 構文の省略可能な引数の一部は UDT の作成を参照しており、別名データ型 (基本型など) の作成には適用できません。
詳細については、「CREATE TYPE
例
次の Transact-SQL ステートメントは、Point 型を作成します。
EXTERNAL NAME は、<assembly_name>.<udt_name>の 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 クエリでは、AdventureWorks2022 データベースで UDT を使用するすべての列とパラメーターを検索します。
USE AdventureWorks2022;
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
INNER JOIN sys.objects AS o
ON o.object_id = c.object_id
INNER 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 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 列の内容を読み取り可能なテキストに変換できます。 次のクエリでは、Point.cs ファイルの内容を読み取り可能なテキストに変換します。WHERE 句の名前を使用して、結果セットを 1 行に制限します。
SELECT CAST (content AS VARCHAR (8000))
FROM sys.assembly_files
WHERE name = 'PointSource';
結果をコピーしてテキスト エディターに貼り付けると、元の文書に存在していた改行とスペースが保持されていることがわかります。
UDT とアセンブリの管理
UDT の実装を計画するときは、どのメソッドが UDT アセンブリ自体に必要であり、どのメソッドを独立したアセンブリに作成してユーザー定義関数やストアド プロシージャとして実装する必要があるかを検討します。 メソッドを個別のアセンブリに分割すると、テーブルの UDT 列に格納される可能性があるデータに影響を与えずにコードを更新できます。 UDT 列やその他の依存オブジェクトを削除せずに UDT アセンブリを変更できるのは、新しい定義が以前の値を読み取ることができ、型のシグネチャが変更されない場合のみです。
UDT の実装に必要なコードから変更される可能性がある手続き型コードを分離すると、メンテナンスが大幅に簡略化されます。 UDT が機能するために必要なコードのみを含め、UDT 定義を可能な限り単純に保つことで、コードリビジョンやバグ修正のために UDT 自体をデータベースから削除する必要があるリスクが軽減されます。
通貨 UDT と通貨換算関数
AdventureWorks2022 サンプル データベースの Currency UDT には、UDT とそれに関連する関数を構成するための推奨される方法の便利な例が用意されています。
Currency UDT は、特定のカルチャの通貨システムに基づいてお金を処理するために使用され、ドル、ユーロなど、さまざまな通貨の種類を格納できます。 UDT クラスは、カルチャ名を文字列として公開し、金額を decimal データ型として公開します。 必要なシリアル化メソッドは、クラスを定義するアセンブリ内にすべて含まれます。 カルチャ間の通貨換算を実装する関数は、ConvertCurrencyという名前の外部関数として実装され、この関数は別のアセンブリに配置されます。
ConvertCurrency 関数は、AdventureWorks2022 データベース内のテーブルから変換率を取得することによって、その処理を行います。 変換率のソースを変更する必要がある場合、または既存のコードに対して他の変更が必要な場合は、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 登録する必要があります。