CREATE TYPE (Transact-SQL)CREATE TYPE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database 的目前資料庫中建立別名資料類型或使用者定義型別。Creates an alias data type or a user-defined type in the current database in SQL ServerSQL Server or Azure SQL DatabaseAzure SQL Database. 別名資料類型的實作是以 SQL ServerSQL Server 原生系統類型為基礎。The implementation of an alias data type is based on a SQL ServerSQL Server native system type. 使用者定義型別是使用 MicrosoftMicrosoft .NET Framework.NET Framework 通用語言執行平台 (CLR) 中之組件的類別來實作的。A user-defined type is implemented through a class of an assembly in the MicrosoftMicrosoft.NET Framework.NET Framework common language runtime (CLR). 若要將使用者定義型別繫結到它的實作,必須先在 SQL ServerSQL Server 中使用 CREATE ASSEMBLY 來註冊內含該型別之實作的 CLR 組件。To bind a user-defined type to its implementation, the CLR assembly that contains the implementation of the type must first be registered in SQL ServerSQL Server by using CREATE ASSEMBLY.

SQL ServerSQL Server 中,依預設,執行 CLR 程式碼的功能是關閉。The ability to run CLR code is off by default in SQL ServerSQL Server. 您可以建立、修改及卸除參考受控碼模組的資料庫物件,但除非您使用 sp_configure 來啟用 clr enabled 選項;否則,這些參考就不會在 SQL ServerSQL Server 中執行。You can create, modify and drop database objects that reference managed code modules, but these references will not execute in SQL ServerSQL Server unless the clr enabled Option is enabled by using sp_configure.

注意

本主題將討論如何將 .NET Framework CLR 整合至 SQL Server。The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR 整合不適用於 Azure SQL DatabaseSQL DatabaseCLR integration does not apply to Azure SQL DatabaseSQL Database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

schema_nameschema_name
這是別名資料類型或使用者自訂類型所屬的結構描述名稱。Is the name of the schema to which the alias data type or user-defined type belongs.

type_nametype_name
這是別名資料類型或使用者自訂類型的名稱。Is the name of the alias data type or user-defined type. 類型名稱必須符合識別碼的規則。Type names must comply with the rules for identifiers.

base_typebase_type
為別名資料類型依據的 SQL ServerSQL Server 提供的資料類型。Is the SQL ServerSQL Server supplied data type on which the alias data type is based. base_type 為沒有預設值的 sysname,且可以為下列值之一:base_type is sysname, with no default, and can be one of the following values:

bigintbigint binary( n )binary( n ) bitbit char( n )char( n )
datedate datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
decimaldecimal floatfloat imageimage intint
moneymoney nchar( n )nchar( n ) ntextntext numericnumeric
nvarchar( n | max)nvarchar( n | max) realreal smalldatetimesmalldatetime smallintsmallint
smallmoneysmallmoney sql_variantsql_variant texttext timetime
tinyinttinyint uniqueidentifieruniqueidentifier varbinary( n | max)varbinary( n | max) varchar( n | max)varchar( n | max)

base_type 也可以是對應至這些系統資料類型之一的任何資料類型同義字。base_type can also be any data type synonym that maps to one of these system data types.

有效位數precision
如果是 decimalnumeric,這會是一個非負數整數,它指出可儲存的最大十進位數總數,小數點左右兩側都包括在內。For decimal or numeric, is a non-negative integer that indicates the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. 如需詳細資訊,請參閱 decimal 和 numeric (TRANSACT-SQL)For more information, see decimal and numeric (Transact-SQL).

scalescale
如果是 decimalnumeric,這會是一個非負數整數,它指出小數點右側所能儲存的最大十進位數,且它必須小於或等於有效位數。For decimal or numeric, is a non-negative integer that indicates the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision. 如需詳細資訊,請參閱 decimal 和 numeric (TRANSACT-SQL)For more information, see decimal and numeric (Transact-SQL).

NULL | NOT NULLNULL | NOT NULL
指定類型可否保留 Null 值。Specifies whether the type can hold a null value. 若未指定,NULL 是預設值。If not specified, NULL is the default.

assembly_nameassembly_name
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定在通用語言執行平台中參考使用者定義型別實作的 SQL ServerSQL Server 組件。Specifies the SQL ServerSQL Server assembly that references the implementation of the user-defined type in the common language runtime. assembly_name 必須符合目前資料庫中 SQL ServerSQL Server 的現有組件。assembly_name should match an existing assembly in SQL ServerSQL Server in the current database.

注意

自主資料庫無法使用 EXTERNAL_NAME。EXTERNAL_NAME is not available in a contained database.

[.[. class_name ]class_name ]
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定組件內實作使用者自訂類型的類別。Specifies the class within the assembly that implements the user-defined type. class_name 必須是有效的識別碼,且必須以類別的形式存在於可以顯示的組件中。class_name must be a valid identifier and must exist as a class in the assembly with assembly visibility. 不論資料庫定序為何,class_name 都要區分大小寫,且必須完全符合相對應組件中的類別名稱。class_name is case-sensitive, regardless of the database collation, and must exactly match the class name in the corresponding assembly. 如果用來撰寫類別的程式設計語言使用命名空間概念 (如 C#),類別名稱可以是一個以方括弧 ( [ ] ) 括住之符合命名空間資格的名稱。The class name can be a namespace-qualified name enclosed in square brackets ([ ]) if the programming language that is used to write the class uses the concept of namespaces, such as C#. 如果未指定 class_nameSQL ServerSQL Server 會假設它和 type_name 一樣。If class_name is not specified, SQL ServerSQL Server assumes it is the same as type_name.

<column_definition><column_definition>
定義使用者定義資料表類型的資料行。Defines the columns for a user-defined table type.

<data type><data type>
針對使用者定義資料表類型定義資料行中的資料類型。Defines the data type in a column for a user-defined table type. 如需資料類型的詳細資訊,請參閱資料類型 (Transact-SQL)For more information about data types, see Data Types (Transact-SQL). 如需資料表的詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information about tables, see CREATE TABLE (Transact-SQL).

<column_constraint><column_constraint>
定義使用者定義資料表類型的資料行條件約束。Defines the column constraints for a user-defined table type. 支援的條件約束包括 PRIMARY KEY、UNIQUE 和 CHECK。Supported constraints include PRIMARY KEY, UNIQUE, and CHECK. 如需資料表的詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information about tables, see CREATE TABLE (Transact-SQL).

<computed_column_definition><computed_column_definition>
將計算資料行運算式定義為使用者定義資料表類型中的資料行。Defines a computed column expression as a column in a user-defined table type. 如需資料表的詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information about tables, see CREATE TABLE (Transact-SQL).

<table_constraint><table_constraint>
定義使用者定義資料表類型上的資料表條件約束。Defines a table constraint on a user-defined table type. 支援的條件約束包括 PRIMARY KEY、UNIQUE 和 CHECK。Supported constraints include PRIMARY KEY, UNIQUE, and CHECK.

<index_option><index_option>
指定在唯一叢集或唯一非叢集索引的多資料列插入作業中,對於索引鍵值重複的錯誤回應。Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. 如需索引選項的詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information about index options, see CREATE INDEX (Transact-SQL).

INDEX *index_name* [ CLUSTERED | NONCLUSTERED ] (*column_name* [ ASC | DESC ] [ ,... *n* ] )

適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

指定要在資料表上建立索引。Specifies to create an index on the table. 這可以是叢集的索引或非叢集索引。This can be a clustered index, or a nonclustered index. 索引將包含列示的資料行,並會以遞增或遞減順序來排序資料。The index will contain the columns listed, and will sort the data in either ascending or descending order.

INDEXINDEX
您必須指定資料行和資料表索引,做為 CREATE TABLE 陳述式的一部分。You must specify column and table indexes as part of the CREATE TABLE statement. 記憶體最佳化資料表不支援 CREATE INDEX 和 DROP INDEX。CREATE INDEX and DROP INDEX are not supported for memory-optimized tables.

MEMORY_OPTIMIZEDMEMORY_OPTIMIZED
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

指出資料表類型是否為記憶體最佳化。Indicates whether the table type is memory optimized. 此選項預設關閉;資料表 (類型) 不是記憶體最佳化的資料表 (類型)。This option is off by default; the table (type) is not a memory optimized table (type). 記憶體最佳化的資料表類型是記憶體最佳化的使用者資料表,其結構描述保存在磁碟上,類似於其他使用者資料表。Memory optimized table types are memory-optimized user tables, the schema of which is persisted on disk similar to other user tables.

BUCKET_COUNTBUCKET_COUNT
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

指出應該在雜湊索引中建立的貯體數目。Indicates the number of buckets that should be created in the hash index. 雜湊索引中 BUCKET_COUNT 的最大值是 1,073,741,824。The maximum value for BUCKET_COUNT in hash indexes is 1,073,741,824. 如需貯體計數的詳細資訊,請參閱經記憶體最佳化之資料表上的索引For more information about bucket counts, see Indexes for Memory-Optimized Tables. bucket_count 為必要的引數。bucket_count is a required argument.

HASHHASH
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更新版本和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

表示已建立雜湊索引。Indicates that a HASH index is created. 只有記憶體最佳化的資料表才支援雜湊索引。Hash indexes are supported only on memory optimized tables.

RemarksRemarks

assembly_name 中參考的組件類別及其方法,應符合在 SQL ServerSQL Server 中實作使用者定義型別的所有需求。The class of the assembly that is referenced in assembly_name, together with its methods, should satisfy all the requirements for implementing a user-defined type in SQL ServerSQL Server. 如需這些需求的詳細資訊,請參閱 CLR 使用者定義型別For more information about these requirements, see CLR User-Defined Types.

其他考量包括以下各項:Additional considerations include the following:

  • 類別可以有多載的方法,但只能從 Managed 程式碼內 (不能從 Transact-SQLTransact-SQL 內) 呼叫這些方法。The class can have overloaded methods, but these methods can be called only from within managed code, not from Transact-SQLTransact-SQL.

  • 如果 assembly_name 是 SAFE 或 EXTERNAL_ACCESS,任何靜態成員都必須宣告為 constreadonlyAny static members must be declared as const or readonly if assembly_name is SAFE or EXTERNAL_ACCESS.

在資料庫內,只能有一個依照已從 CLR 上傳至 SQL ServerSQL Server 中之任何指定類型來註冊的使用者自訂類型。Within a database, there can be only one user-defined type registered against any specified type that has been uploaded in SQL ServerSQL Server from the CLR. 如果使用者自訂類型建立在某 CLR 類型上,而針對該 CLR 類型,在資料庫中已存在使用者自訂類型,則 CREATE TYPE 會因錯誤而失敗。If a user-defined type is created on a CLR type for which a user-defined type already exists in the database, CREATE TYPE fails with an error. 如果 CLR 類型可以對應至多個使用者自訂類型,就需要利用這項限制來避免 SQL 類型解析期間的模稜兩可。This restriction is required to avoid ambiguity during SQL Type resolution if a CLR type can be mapped to more than one user-defined type.

如果類型中有任何 mutator 方法沒有傳回 void,CREATE TYPE 陳述式就不會執行。If any mutator method in the type does not return void, the CREATE TYPE statement does not execute.

若要修改使用者自訂類型,您必須利用 DROP TYPE 陳述式卸除該類型,然後重新建立它。To modify a user-defined type, you must drop the type by using a DROP TYPE statement and then re-create it.

public 資料庫角色與使用 sp_addtype 建立的使用者定義型別不同,前者不會在使用 CREATE TYPE 建立的類型上自動被授與 REFERENCES 權限。Unlike user-defined types that are created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. 這個權限必須另外授與。This permission must be granted separately.

在使用者定義的資料表類型中,column_name <資料類型> 中使用的結構化使用者定義型別屬於資料表類型定義所在之資料庫結構描述範圍的一部分。In user-defined table types, structured user-defined types that are used in column_name <data type> are part of the database schema scope in which the table type is defined. 若要在資料庫中存取不同範圍內的結構化使用者定義型別,請使用兩部分的名稱。To access structured user-defined types in a different scope within the database, use two-part names.

在使用者定義的資料表類型中,計算資料行的主索引鍵必須是 PERSISTED 和 NOT NULL。In user-defined table types, the primary key on computed columns must be PERSISTED and NOT NULL.

記憶體最佳化的資料表類型Memory-Optimized Table Types

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 開始,資料表類型的資料處理可以在主要記憶體中執行,而不是在磁碟上。Beginning in SQL Server 2014 (12.x)SQL Server 2014 (12.x), processing data in a table type can be done in primary memory, and not on disk. 如需詳細資訊,請參閱記憶體內部 OLTP (記憶體內部最佳化)For more information, see In-Memory OLTP (In-Memory Optimization). 如需如何建立經記憶體最佳化的資料表類型的範例程式碼,請參閱建立經記憶體最佳化的資料表和原生編譯的預存程序For code samples showing how to create memory-optimized table types, see Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.

權限Permissions

需要目前資料庫的 CREATE TYPE 權限,以及 schema_name的 ALTER 權限。Requires CREATE TYPE permission in the current database and ALTER permission on schema_name. 如果未指定 schema_name ,則套用用來判斷目前使用者之結構描述的預設名稱解析規則。If schema_name is not specified, the default name resolution rules for determining the schema for the current user apply. 如果指定 assembly_name,則使用者必須擁有該組件,或必須有在該組件上的 REFERENCES 權限。If assembly_name is specified, a user must either own the assembly or have REFERENCES permission on it.

如果將 CREATE TABLE 陳述式中的任何資料行定義成使用者定義型別,則需要使用者定義型別的 REFERENCES 權限。If any columns in the CREATE TABLE statement are defined to be of a user-defined type, REFERENCES permission on the user-defined type is required.

注意

一位使用者建立一張資料表,裡面有一個資料行採用使用者定義的型別,這位使用者需要 REFERENCES 權限。A user creating a table with a column that uses a user-defined type needs the REFERENCES permission on the user-defined type. 如果此資料表必須建立在 TempDB 中,則每次建立資料表之前,必須必須明確授與 REFERENCES 權限,或者此資料類型和 REFERENCES 權限需要加入至模型資料庫。If this table must be created in TempDB, then either the REFERENCES permission needs to be granted explicitly each time before the table is created, or this data type and REFERENCES permissions need to be added to the Model database. 如果這樣做,則 TempDB 便永遠能取得此資料類型和權限。If this is done, then this data type and permissions will be available in TempDB permanently. 否則,當 SQL Server 重新啟動時,使用者定義的資料類型和權限就會消失。Otherwise, the user-defined data type and permissions will disappear when SQL Server is restarted. 如需詳細資訊,請參閱 CREATE TABLEFor more information, see CREATE TABLE

範例Examples

A.A. 根據 varchar 資料類型建立別名類型Creating an alias type based on the varchar data type

下列範例根據系統提供的 varchar 資料類型建立別名資料類型。The following example creates an alias type based on the system-supplied varchar data type.

CREATE TYPE SSN  
FROM varchar(11) NOT NULL ;  

B.B. 建立使用者自訂類型Creating a user-defined type

下列範例建立 Utf8String 類型,它參考 utf8string 組件中的 utf8string 類別。The following example creates a type Utf8String that references class utf8string in the assembly utf8string. 建立該類型之前,必須先在本機資料庫中註冊 utf8string 組件。Before creating the type, assembly utf8string is registered in the local database. 以有效的描述取代 CREATE ASSEMBLY 陳述式的二進位部分。Replace the binary portion of the CREATE ASSEMBLY statement with a valid description.

適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

CREATE ASSEMBLY utf8string  
AUTHORIZATION [dbi]   
FROM 0x4D... ;  
GO  
CREATE TYPE Utf8String   
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;  
GO  

C.C. 建立使用者自訂資料表類型Creating a user-defined table type

下列範例會建立有兩個資料行的使用者定義資料表類型。The following example creates a user-defined table type that has two columns. 如需如何建立及使用資料表值參數的詳細資訊,請參閱使用資料表值參數 (Database Engine)For more information about how to create and use table-valued parameters, see Use Table-Valued Parameters (Database Engine).

CREATE TYPE LocationTableType AS TABLE   
    ( LocationName VARCHAR(50)  
    , CostRate INT );  
GO  

D.D. 使用主索引鍵和索引來建立使用者定義的資料表類型Creating a user-defined table type with primary key and index

以下範例會建立有三個資料行的使用者定義資料表類型,其中一個 (Name) 是主索引鍵,另一個 (Price) 則是非叢集索引。The following example creates a user-defined table type that has three columns, one of which (Name) is the primary key and another (Price) has a nonclustered index. 如需如何建立及使用資料表值參數的詳細資訊,請參閱使用資料表值參數 (Database Engine)For more information about how to create and use table-valued parameters, see Use Table-Valued Parameters (Database Engine).

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

另請參閱See Also

CREATE ASSEMBLY (Transact-SQL) CREATE ASSEMBLY (Transact-SQL)
DROP TYPE (Transact-SQL) DROP TYPE (Transact-SQL)
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)