CREATE TYPE (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

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

Note

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

Transact-SQL 構文表記規則

構文

ユーザー定義データ型の構文:

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 ] )

ユーザー定義のメモリ最適化テーブルの種類の構文:

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 で、既定値はありません。有効値は次のとおりです。

  • bigintintsmallint、tinyint
  • binary(n)varbinary(n)、varbinary (max)
  • bit
  • char(n)nchar(n)nvarchar(n)nvarchar(max)varchar(n)、varchar (max)
  • datedatetimedatetime2datetimeoffsetsmalldatetime、および time
  • decimal および numeric
  • floatreal
  • image
  • moneysmallmoney
  • sql_variant
  • text および ntext
  • uniqueidentifier

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

有効桁数 (precision)

10 進数または数値の場合有効桁数は、小数点の左と右の両方に格納できる 10 進数の最大合計数を示す負以外の整数です。 詳細については、「decimal 型と numeric 型 (Transact-SQL)」を参照してください。

scale

10 進数または数値の場合小数点以下桁数は、小数点の右側に格納できる 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がある場合EXTERNAL_ACCESSSAFEは、静的メンバーを const または readonly として宣言する必要があります。

データベース内では、SQL Server で CLR からアップロードされた任意の指定された型に対して、1 つのユーザー定義型のみを登録できます。 ユーザー定義型がデータベースに既に存在する CLR 型に対してユーザー定義型が作成された場合、 CREATE TYPE エラーで失敗します。 この制約が必要なのは、CLR 型を複数のユーザー定義型にマップすることが可能な場合の、SQL 型の解決時のあいまいな状態を避けるためです。

型のミューテーター メソッドが void を返さない場合、CREATE TYPEステートメントは実行されません。

ユーザー定義型を変更するには、ステートメントを使用 DROP TYPE して型を削除してから再作成する必要があります。

使用sp_addtypeして作成されるユーザー定義型とは異なり、パブリック データベース ロールには、使用してCREATE TYPE作成された型に対するアクセス許可が自動的に付与REFERENCESされません。 この権限は個別に付与する必要があります。

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

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

メモリ最適化テーブル型

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

アクセス許可

現在のデータベースのアクセス許可と、ALTERschema_nameに対するアクセス許可が必要CREATE TYPEです。 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