タイプ作成(Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

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やMicrosoft FabricのSQLデータベースには適用されません。これらの場合はCLR(.NET)タイプがサポートされていません。

Transact-SQL 構文表記規則

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

Arguments

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

Remarks

assembly_name とそのメソッドで参照されているアセンブリのクラスは、SQL Server でユーザー定義型を実装するためのすべての要件を満たしている必要があります。 これらの要件の詳細については、「CLR ユーザー定義型」を参照してください。

その他、次のような注意事項があります。

• クラスにはオーバーロードされたメソッドを含めることができますが、これらのメソッドは、Transact-SQL からではなく、マネージド コード内からのみ呼び出すことができます。

• assembly_nameがまたはの場合はSAFEreadonly として宣言する必要があります。

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

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

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

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

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

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

Fabric SQL データベースではユーザー定義型を作成できますが、Fabric OneLake にはミラー化されず、ユーザー定義型の列はミラーリングでスキップされます。

メモリ最適化テーブル型

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

Permissions

現在のデータベースCREATE TYPE権限と、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

または、一時ストレージのニーズに合わせてユーザー定義データ型を参照する必要がある場合は、一時テーブルを使用する代わりに、テーブル変数の使用を検討してください。 ユーザー定義データ型を参照するテーブル変数の場合、ユーザー定義データ型に対するアクセス許可を明示的に付与する必要はありません。

Examples

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

関連コンテンツ

• CREATE ASSEMBLY (Transact-SQL) (アセンブリを作成)
• ドロップタイプ(Transact-SQL)
• イベントデータ (Transact-SQL)
• CLR ユーザー定義型
• SQL Server でのユーザー定義型の使用