CREATE TYPE (Transact-SQL)
適用対象: SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL Server または Azure SQL Database で別名データ型またはユーザー定義型を現在のデータベースで作成します。 別名データ型の実装は、データベース エンジンネイティブ システム型に基づいています。 ユーザー定義型は、Microsoft.NET Framework 共通言語ランタイム (CLR) のアセンブリのクラスを使用して実装します。 ユーザー定義型を実装にバインドするには、その型の実装を含む CLR アセンブリを、まず CREATE ASSEMBLY を使用してデータベース エンジンに登録する必要があります。
既定では、SQL Server の CLR コード実行機能は無効になっています。 マネージド コード モジュールを参照するデータベース オブジェクトを作成、変更、および削除できます。 ただし、 clr enabled Option が sp_configure を使用して有効になっていない限り、これらの参照は SQL Server では実行されません。
Note
このトピックでは、SQL Server への .NET Framework CLR の統合について説明します。 CLR 統合は、Azure SQL Database には適用されません。
構文
ユーザー定義データ型の構文:
CREATE TYPE [ schema_name. ] type_name
{
FROM base_type
[ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
| EXTERNAL NAME assembly_name [ .class_name ]
| AS TABLE ( { <column_definition> | <computed_column_definition> [ , ...n ]
[ <table_constraint> ] [ , ...n ]
[ <table_index> ] [ , ...n ] } )
} [ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH ( <index_option> [ , ...n ] )
]
| CHECK ( logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH ( <index_option> [ , ...n ] )
]
| CHECK ( logical_expression )
]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column [ ASC | DESC ] [ , ...n ] )
[
WITH ( <index_option> [ , ...n ] )
]
| CHECK ( logical_expression )
}
<index_option> ::=
{
IGNORE_DUP_KEY = { ON | OFF }
}
< table_index > ::=
INDEX index_name
[ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , ...n ] )
[INCLUDE (column, ...n)]
ユーザー定義のメモリ最適化テーブルの種類の構文:
CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }
| [ <table_constraint> ] [ , ...n ]
| [ <table_index> ] [ , ...n ] )
[ WITH ( <table_option> [ , ...n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ] [ NULL | NOT NULL ]
[ IDENTITY [ (1 , 1) ]
]
[ <column_constraint> [ , ...n ] ] [ <column_index> ]
<data type> ::=
[ type_schema_name . ] type_name [ ( precision [ , scale ] ) ]
<column_constraint> ::=
{ PRIMARY KEY { NONCLUSTERED HASH WITH ( BUCKET_COUNT = bucket_count )
| NONCLUSTERED }
}
< table_constraint > ::=
{ PRIMARY KEY { NONCLUSTERED HASH (column [ , ...n ] )
WITH ( BUCKET_COUNT = bucket_count )
| NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}
}
<column_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
| NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}
< table_index > ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
| [ NONCLUSTERED ] ( column [ ASC | DESC ] [ , ...n ] )
}
<table_option> ::=
{
[ MEMORY_OPTIMIZED = { ON | OFF } ]
}
Note
SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。
引数
schema_name
別名データ型またはユーザー定義型が属するスキーマの名前。
type_name
別名データ型またはユーザー定義型の名前。 型の名前は、識別子の規則に従っている必要があります。
base_type
データベース エンジンエイリアス データ型の基になっている指定されたデータ型です。 base_type のデータ型は sysname で、既定値はありません。有効値は次のとおりです。
- bigint、 int、 smallint、および tinyint
- binary(n)、 varbinary(n)、および varbinary(max)
- bit
- char(n)、 nchar(n)、 nvarchar(n)、 nvarchar(max)、 varchar(n)、および varchar(max)
- date、 datetime、 datetime2、 datetimeoffset、 smalldatetime、および time
- decimal および numeric
- float と real
- image
- money と smallmoney
- sql_variant
- text および ntext
- uniqueidentifier
また base_type は、これらのシステム データ型のいずれかにマップする任意のデータ型のシノニムにすることもできます。
有効桁数 (precision)
decimal または numeric の場合、precision は、小数点の左と右の両方に格納できる 10 進数の最大数を示す負以外の整数です。 詳細については、「decimal 型と numeric 型 (Transact-SQL)」を参照してください。
scale
decimal または numeric の場合、scale は、小数点の右側に格納できる 10 進数の最大数を示す負以外の整数であり、有効桁数以下である必要があります。 詳細については、「decimal 型と numeric 型 (Transact-SQL)」を参照してください。
NULL | NOT NULL
型が NULL 値を保持できるかどうかを指定します。 指定しない場合の既定は、NULL
です。
assembly_name
適用対象: SQL Server
共通言語ランタイム内のユーザー定義型の実装を参照する SQL Server のアセンブリを指定します。 assembly_name は、SQL Server の現在のデータベース内の既存のアセンブリに一致している必要があります。
Note
EXTERNAL_NAME
は包含データベースでは使用できません。
[ . class_name ]
適用対象: SQL Server
ユーザー定義型を実装するアセンブリ内のクラスを指定します。 class_name は有効な識別子であり、アセンブリ内にアセンブリで可視のクラスとして存在している必要があります。 class_name は、対応するアセンブリ内のクラス名に正確に一致している必要があります。大文字と小文字は、データベース照合順序に関係なく区別されます。 クラスの記述に使用されている C# などのプログラミング言語が、名前空間の概念を使用している場合、クラス名は、角かっこ ( [ ] ) で囲まれた名前空間で修飾された名前にすることができます。 class_nameが指定されていない場合、SQL Server ではtype_nameと同じであると見なされます。
<column_definition>
ユーザー定義テーブル型の列を定義します。
<データ型>
ユーザー定義テーブル型の列でデータ型を定義します。 データ型の詳細については、「データ型 (Transact-SQL)」を参照してください。 テーブルの詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。
<column_constraint>
ユーザー定義テーブル型の列制約を定義します。 サポートされる制約には、 PRIMARY KEY
、 UNIQUE
、および CHECK
が含まれます。 テーブルの詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。
<computed_column_definition>
計算列の式をユーザー定義テーブル型の列として定義します。 テーブルの詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。
<table_constraint>
ユーザー定義テーブル型のテーブル制約を定義します。 サポートされる制約には、 PRIMARY KEY
、 UNIQUE
、および CHECK
が含まれます。
<index_option>
一意のクラスター化インデックスまたは一意の非クラスター化インデックスにおいて、複数行の挿入操作で重複したキー値が見つかった場合の、エラー応答を指定します。 インデックス オプションの詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。
INDEX index_name [ CLUSTERED |NONCLUSTERED ] ( column_name [ ASC |DESC ] [ , ....n ] )
適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance。
テーブル上にインデックスを作成することを指定します。 これには、クラスター化インデックスまたは非クラスター化インデックスを指定できます。 インデックスには一覧表示されている列が含まれており、データは昇順または降順で並べ替えられます。
INDEX
CREATE TABLE
ステートメントの一部として列インデックスとテーブル インデックスを指定する必要があります。 CREATE INDEX
DROP INDEX
は、メモリ最適化テーブルではサポートされていません。
MEMORY_OPTIMIZED
適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、および Azure SQL Managed Instance。 Azure SQL Managed Instance では、General Purpose レベルのメモリ最適化テーブルはサポートされていません。
テーブル型がメモリ最適化かどうかを示します。 このオプションは既定ではオフになっています。テーブル (型) はメモリ最適化テーブル (型) ではありません。 メモリ最適化テーブル型は、他のユーザー テーブルと同様にスキーマがディスク上に保存されるメモリ最適化ユーザー テーブルです。
BUCKET_COUNT
適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、Azure SQL Database、および Azure SQL Managed Instance。
ハッシュ インデックスに作成されるバケットの数を示します。 ハッシュ インデックスの BUCKET_COUNT
の最大値は 1,073,741,824 です。 バケット数の詳細については、「 メモリ最適化テーブルのインデックスを参照してください。 bucket_count は必須の引数です。
HASH
適用対象: SQL Server 2014 (12.x) 以降のバージョン、Azure SQL Database、Azure SQL Database、および Azure SQL Managed Instance。
HASH
インデックスが作成されることを示します。 ハッシュ インデックスは、メモリ最適化テーブルでのみサポートされます。
解説
assembly_name とそのメソッドで参照されているアセンブリのクラスは、SQL Server でユーザー定義型を実装するためのすべての要件を満たしている必要があります。 これらの要件の詳細については、「CLR ユーザー定義型」を参照してください。
その他、次のような注意事項があります。
クラスにはオーバーロードされたメソッドを含めることができますが、これらのメソッドは、Transact-SQL からではなく、マネージド コード内からのみ呼び出すことができます。
assembly_nameが
SAFE
またはEXTERNAL_ACCESS
の場合は静的メンバーを const または readonly として宣言する必要があります。
データベース内では、SQL Server で CLR からアップロードされた任意の指定された型に対して、1 つのユーザー定義型のみを登録できます。 ユーザー定義型がデータベースに既に存在する CLR 型に対してユーザー定義型が作成されている場合、 CREATE TYPE
はエラーで失敗します。 この制約が必要なのは、CLR 型を複数のユーザー定義型にマップすることが可能な場合の、SQL 型の解決時のあいまいな状態を避けるためです。
型のミューテーター メソッドが void を返さない場合、 CREATE TYPE
ステートメントは実行されません。
ユーザー定義型を変更するには、 DROP TYPE
ステートメントを使用して型を削除してから、再作成する必要があります。
sp_addtype
を使用して作成されたユーザー定義型とは異なり、public データベース ロールには、CREATE TYPE
を使用して作成された型に対するREFERENCES
権限は自動的に付与されません。 この権限は個別に付与する必要があります。
ユーザー定義テーブル型の場合、column_name<data type> で使用される構造化ユーザー定義型は、テーブル型が定義されているデータベース スキーマ スコープの一部になります。 データベース内の別のスコープに含まれている構造化ユーザー定義型にアクセスするには、2 つの部分から構成される名前を使用します。
ユーザー定義テーブル型では、計算列の主キーは PERSISTED
し、 NOT NULL
する必要があります。
メモリ最適化テーブル型
SQL Server 2014 (12.x) 以降では、ディスク上ではなく、プライマリ メモリ内でテーブル型のデータを処理できます。 詳細については、「 In-Memory OLTP の概要と使用シナリオを参照してください。 メモリ最適化テーブル型の作成方法を示すコード サンプルについては、「メモリ最適化テーブルおよびネイティブ コンパイル ストアド プロシージャの作成」をご覧ください。
アクセス許可
現在のデータベースCREATE TYPE
権限と、schema_nameに対するALTER
権限が必要です。 schema_name指定されていない場合は、現在のユーザーのスキーマを決定するための既定の名前解決規則が適用されます。 assembly_nameが指定されている場合、ユーザーはアセンブリを所有しているか、REFERENCES
権限を持っている必要があります。
CREATE TABLE
ステートメント内の列をユーザー定義型として定義する場合は、そのユーザー定義型に対する REFERENCES
権限が必要です。
ユーザー定義型を使用する列を持つテーブルを作成するユーザーには、ユーザー定義型に対する REFERENCES
権限が必要です。 このテーブルを tempdb
で作成する必要がある場合は、 REFERENCES
アクセス許可を 前に明示的に付与するか テーブルを作成するたびに、このデータ型と REFERENCES
アクセス許可を model
データベースに追加する必要があります。 次に例を示します。
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public
これを行うと、このデータ型と REFERENCES
アクセス許可が tempdb
永続的に使用できるようになります。 この処理が完了していない場合、SQL Server の再起動時にユーザー定義のデータ型とアクセス許可は消去されます。 詳細については、「CREATE TABLE」を参照してください。
すべての新しいデータベースで、このユーザー定義データ型の定義とアクセス許可をモデルから継承したくない場合は、スタートアップ ストアド プロシージャを使用して、 tempdb
データベースでのみ適切なアクセス許可を作成して割り当てることができます。 次に例を示します。
USE master
GO
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
GO
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
GO
または、一時ストレージのニーズに合わせてユーザー定義データ型を参照する必要がある場合は、一時テーブルを使用する代わりに、テーブル変数の使用を検討してください。 ユーザー定義データ型を参照するテーブル変数の場合、ユーザー定義データ型に対するアクセス許可を明示的に付与する必要はありません。
例
A. varchar データ型に基づいてエイリアス型を作成する
次の例では、システムから提供されている varchar
データ型に基づいて、別名型を作成します。
CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;
B. ユーザー定義型を作成する
適用対象: SQL Server
次の例では、アセンブリ utf8string
内のクラス utf8string
を参照する型 Utf8String
を作成します。 型を作成する前に、アセンブリ utf8string
がローカル データベースに登録されます。 CREATE ASSEMBLY
ステートメントのバイナリ部分を有効な説明に置き換えます。
CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO
CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO
C: ユーザー定義テーブル型を作成する
次の例では、2 つの列を持つユーザー定義テーブル型が作成されます。 テーブル値パラメーターの作成方法および使用方法の詳細については、「テーブル値パラメーターの使用 (データベース エンジン)」を参照してください。
CREATE TYPE LocationTableType AS TABLE (
LocationName VARCHAR(50),
CostRate INT
);
GO
D. 主キーとインデックスを使用してユーザー定義テーブル型を作成する
次の例では、3 つの列を持つユーザー定義テーブル型を作成します。そのうちの 1 つ (Name
) はプライマリ キーで、もう 1 つ (Price
) には非クラスター化インデックスが作成されています。 テーブル値パラメーターの作成方法および使用方法の詳細については、「テーブル値パラメーターの使用 (データベース エンジン)」を参照してください。
CREATE TYPE InventoryItem AS TABLE (
[Name] NVARCHAR(50) NOT NULL,
SupplierId BIGINT NOT NULL,
Price DECIMAL(18, 4) NULL,
PRIMARY KEY (Name),
INDEX IX_InventoryItem_Price(Price)
);
GO