CREATE TYPE (Transact-SQL)

[アーティクル]
04/26/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server または Azure SQL Database で別名データ型またはユーザー定義型を現在のデータベースで作成します。別名データ型の実装は、SQL Server のネイティブシステム型に基づきます。ユーザー定義型は、Microsoft.NET Framework 共通言語ランタイム (CLR) のアセンブリのクラスを使用して実装します。ユーザー定義型を実装にバインドするには、先に CREATE ASSEMBLY を使用して、その実装を含む CLR アセンブリを SQL Server で登録しておく必要があります。

既定では、SQL Server の CLR コード実行機能は無効になっています。マネージドコードモジュールを参照するデータベースオブジェクトを作成、変更、および削除できますが、それらの参照を SQL Server で実行するには、sp_configure を使用して clr enabled オプションを有効にする必要があります。

注意

このトピックでは、SQL Server への .NET Framework CLR の統合について説明します。 CLR 統合は、Azure SQL Database には適用されません。

Transact-SQL 構文表記規則

構文

-- User-defined Data Type syntax    
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 constraint_name  
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )} }

-- User-defined Memory Optimized Table Types syntax  
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 WITH ( BUCKET_COUNT = bucket_count ) 
     | NONCLUSTERED 
}  
  
< table_index > ::=  
  INDEX constraint_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 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

schema_name
別名データ型またはユーザー定義型が所属しているスキーマの名前です。

type_name
別名データ型またはユーザー定義型の名前です。型の名前は、識別子の規則に従っている必要があります。

base_type
別名データ型の基になる SQL Server 提供のデータ型です。 base_type のデータ型は sysname で、既定値はありません。有効値は次のとおりです。

bigint
binary(n)
bit
char(n)

date
datetime
datetime2
datetimeoffset

decimal
float
image
int

money
nchar(n)
ntext
numeric

nvarchar(n | max)
real
smalldatetime
smallint

smallmoney
sql_variant
text
time

tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)

また base_type は、これらのシステムデータ型のいずれかにマップする任意のデータ型のシノニムにすることもできます。

有効桁数 (precision)
decimal 型または numeric 型の場合は、格納可能な 10 進数の最大桁数を示す、負ではない整数です。これは、小数点の左側と右側の桁数の合計です。詳細については、「decimal 型と numeric 型 (Transact-SQL)」を参照してください。

scale
decimal 型または numeric 型の場合は、小数点の右側にとることができる 10 進数の最大桁数を示す、負ではない整数です。有効桁数以下の数値である必要があります。詳細については、「decimal 型と numeric 型 (Transact-SQL)」を参照してください。

NULL | NOT NULL
型が NULL 値を保持できるかどうかを指定します。指定しない場合は、NULL が既定値です。

assembly_name
適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降)。

共通言語ランタイム内のユーザー定義型の実装を参照する SQL Server のアセンブリを指定します。 assembly_name は、SQL Server の現在のデータベース内の既存のアセンブリに一致している必要があります。

注意

EXTERNAL_NAME は、包含データベースでは使用できません。

[.class_name]
適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降)。

ユーザー定義型を実装するアセンブリ内のクラスを指定します。 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 (SQL Server 2014 (12.x) 以降)、Azure SQL データベースおよび Azure SQL Managed Instance。

テーブル上にインデックスを作成することを指定します。これには、クラスター化インデックスまたは非クラスター化インデックスを指定できます。インデックスには一覧表示される列が含まれ、昇順、降順のいずれかでデータが並べ替えられます。

INDEX
CREATE TABLE ステートメントの一部として列インデックスとテーブルインデックスを指定する必要があります。メモリ最適化テーブルでは、CREATE INDEX および DROP INDEX はサポートされません。

MEMORY_OPTIMIZED
適用対象: SQL Server (SQL Server 2014 (12.x) 以降)、Azure SQL データベースおよび Azure SQL Managed Instance。 Azure SQL Managed Instance では、General Purpose 層でのメモリ最適化テーブルはサポートされていません。

テーブル型がメモリ最適化かどうかを示します。既定では、このオプションはオフになっています。テーブル (型) は、メモリ最適化テーブル (型) ではありません。メモリ最適化テーブル型は、他のユーザーテーブルと同様にスキーマがディスク上に保存されるメモリ最適化ユーザーテーブルです。

BUCKET_COUNT
適用対象: SQL Server (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 (SQL Server 2014 (12.x) 以降) と Azure SQL Database、Azure SQL Database、および Azure SQL Managed Instance。

ハッシュインデックスを作成することを示します。ハッシュインデックスは、メモリ最適化テーブルでのみサポートされます。

解説

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 を使用して作成したユーザー定義型と異なり、CREATE TYPE を使用して作成した型に対しては、データベースロール public に REFERENCES 権限が自動的に付与されるわけではありません。この権限は個別に付与する必要があります。

ユーザー定義テーブル型の場合、column_name<data type> で使用される構造化ユーザー定義型は、テーブル型が定義されているデータベーススキーマスコープの一部になります。データベース内の別のスコープに含まれている構造化ユーザー定義型にアクセスするには、2 つの部分から構成される名前を使用します。

ユーザー定義テーブル型では、計算列の主キーを PERSISTED および NOT NULL にする必要があります。

メモリ最適化テーブル型

SQL Server 2014 (12.x) 以降では、ディスク上ではなく、プライマリメモリ内でテーブル型のデータを処理できます。詳細については、「インメモリ OLTP (インメモリ最適化)」を参照してください。メモリ最適化テーブル型の作成方法を示すコードサンプルについては、「メモリ最適化テーブルおよびネイティブコンパイルストアドプロシージャの作成」をご覧ください。

アクセス許可

現在のデータベース内の CREATE TYPE 権限、および schema_nameに対する ALTER 権限が必要です。 schema_name を指定しなかった場合は、現在のユーザーのスキーマを判断するための既定の名前解決ルールが適用されます。 assembly_name を指定した場合は、ユーザーがそのアセンブリの所有者であるか、そのアセンブリに対する REFERENCES 権限を持っている必要があります。

CREATE TABLE ステートメント内の列をユーザー定義型として定義する場合は、そのユーザー定義型に対する REFERENCES 権限が必要です。

注意

ユーザー定義型を使用する列があるテーブルを作成するユーザーは、そのユーザー定義型に対して REFERENCES アクセス許可を持っている必要があります。このテーブルを TempDB 内に作成する必要がある場合、テーブルを作成する前に毎回 REFERENCES アクセス許可を明示的に付与する必要があります。または、このデータ型と REFERENCES アクセス許可を model データベースに追加する必要があります。この処理が完了すると、このデータ型とアクセス許可は TempDB で永続的に利用できるようになります。この処理が完了していない場合、SQL Server の再起動時にユーザー定義のデータ型とアクセス許可は消去されます。詳細については、「CREATE TABLE」を参照してください。

例

A. varchar データ型に基づいた別名型を作成する

次の例では、システムから提供されている varchar データ型に基づいて、別名型を作成します。

CREATE TYPE SSN  
FROM varchar(11) NOT NULL ;

B. ユーザー定義型を作成する

次の例では、アセンブリ utf8string 内のクラス utf8string を参照する型 Utf8String を作成します。型を作成する前に、アセンブリ utf8string がローカルデータベースに登録されます。 CREATE ASSEMBLY ステートメントのバイナリ部分を有効な記述と置き換えます。

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降)。

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

参照

CREATE ASSEMBLY (Transact-SQL)
DROP TYPE (Transact-SQL)
EVENTDATA (Transact-SQL)
CLR ユーザー定義型
 SQL Server でのユーザー定義型の使用

CREATE TYPE (Transact-SQL)

構文

引数

解説

メモリ最適化テーブル型

アクセス許可

例

A. varchar データ型に基づいた別名型を作成する

B. ユーザー定義型を作成する

C. ユーザー定義テーブル型を作成する

D. プライマリキーとインデックスでユーザー定義テーブル型を作成する

参照

その他のリソース

その他のリソース

CREATE TYPE (Transact-SQL)

構文

引数

解説

メモリ最適化テーブル型

アクセス許可

例

A. varchar データ型に基づいた別名型を作成する

B. ユーザー定義型を作成する

C. ユーザー定義テーブル型を作成する

D. プライマリ キーとインデックスでユーザー定義テーブル型を作成する

参照

その他のリソース

その他のリソース

D. プライマリキーとインデックスでユーザー定義テーブル型を作成する