• 發行項

CREATE TYPE (Transact-SQL)

在目前資料庫中建立別名資料類型或使用者定義型別。別名資料型別的實作是以 SQL Server 原生系統類型為基礎。使用者自訂類型是利用 Microsoft .NET Framework Common Language Runtime (CLR) 中之組件的類別來實作的。若要將使用者定義型別繫結到它的實作，必須先在 SQL Server 中利用 CREATE ASSEMBLY 來註冊內含該型別之實作的 CLR 組件。

[!附註]

在 SQL Server 中，依預設，執行 CLR 程式碼的功能是關閉。您可以建立、修改及卸除參考 Managed 程式碼模組的資料庫物件，但除非您利用 sp_configure 來啟用 clr enabled 選項，否則，這些參考就不會在 SQL Server 中執行。

主題連結圖示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> }
        [ <table_constraint> ] [ ,...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 } 
}

引數

  • 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，這會是一個非負數整數，它指出可儲存的最大十進位數總數，小數點左右兩側都包括在內。如需詳細資訊，請參閱＜decimal 和 numeric (Transact-SQL)＞。

  • scale
    如果是 decimal 或 numeric，這會是一個非負數整數，它指出小數點右側所能儲存的最大十進位數，且它必須小於或等於有效位數。如需詳細資訊，請參閱＜decimal 和 numeric (Transact-SQL)＞。

  • NULL | NOT NULL
    指定類型可否保留 Null 值。若未指定，NULL 是預設值。

  • assembly_name
    指定 SQL Server 組件，來參考 Common Language Runtime 中之使用者自訂類型的實作。assembly_name 應符合目前資料庫之 SQL Server 中的現有組件。

  • [.class_name ]
    指定組件內實作使用者自訂類型的類別。class_name 必須是有效的識別碼，且必須以組件中的類別存在，而組件必須具有組件可見性。不論資料庫定序為何，class_name 都有區分大小寫，且必須完全符合相對應組件中的類別名稱。如果用來撰寫類別的程式設計語言使用命名空間概念 (如 C#)，類別名稱可以是一個以方括號 (    [ ]    ) 括住之符合命名空間資格的名稱。如果未指定 class_name，則 SQL Server 會假設它與 type_name 相同。

  • <column_definition>
    定義使用者定義資料表類型的資料行。如需有關資料行的詳細資訊，請參閱＜新增與刪除資料行＞。

  • <data type>
    針對使用者定義資料表類型定義資料行中的資料類型。如需有關資料類型的詳細資訊，請參閱＜資料類型 (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)＞。

備註

使用 CREATE TYPE 來建立 CLR 使用者自訂類型時，資料庫相容性必須是 90。

assembly_name 中參考的組件類別及其方法，應符合在 SQL Server 中實作使用者自訂類型的所有需求。如需有關這些需求的詳細資訊，請參閱＜CLR 使用者定義型別＞。

其他考量包括以下各項：

  • 類別可以有多載的方法，但只能從 Managed 程式碼內 (不能從 Transact-SQL 內) 呼叫這些方法。

  • 如果 assembly_name 是 SAFE 或 EXTERNAL_ACCESS，任何靜態成員都必須宣告為 const 或 readonly。

在資料庫內，只能有一個依照已從 CLR 上載至 SQL Server 中之任何指定類型來註冊的使用者自訂類型。如果使用者自訂類型建立在某 CLR 類型上，而針對該 CLR 類型，在資料庫中已存在使用者自訂類型，則 CREATE TYPE 會因錯誤而失敗。如果 CLR 類型可以對應至多個使用者自訂類型，就需要利用這項限制來避免 SQL 類型解析期間的模稜兩可。

如果類型中有任何 mutator 方法沒有傳回空值，CREATE TYPE 陳述式就不執行。

若要修改使用者自訂類型，您必須利用 DROP TYPE 陳述式卸除該類型，然後重新建立它。

public 資料庫角色與使用 sp_addtype 建立的使用者定義型別不同，前者不會在使用 CREATE TYPE 建立的類型上自動被授與 REFERENCES 權限。這個權限必須另外授與。

在使用者定義的資料表類型中，column_name <data type> 中使用的結構化使用者定義型別屬於資料表類型定義所在之資料庫結構描述範圍的一部分。若要在資料庫中存取不同範圍內的結構化使用者定義型別，請使用兩部分的名稱。

在使用者定義的資料表類型中，計算資料行的主索引鍵必須是 PERSISTED 和 NOT NULL。

權限

需要在目前資料庫中的 CREATE TYPE 權限，以及在 schema_name 上的 ALTER 權限。如果未指定 schema_name，則套用用來判斷目前使用者之結構描述的預設名稱解析規則。如果指定了 assembly_name，使用者必須擁有該組件，或必須有在該組件上的 REFERENCES 權限。

範例

A. 根據 varchar 資料類型建立別名資料型別

下列範例根據系統提供的 varchar 資料類型建立別名資料型別。

CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

B. 建立使用者自訂類型

下列範例建立 Utf8String 類型，它參考 utf8string 組件中的 utf8string 類別。建立該類型之前，必須先在本機資料庫中註冊 utf8string 組件。

CREATE ASSEMBLY utf8string
FROM '\\ComputerName\utf8string\utf8string.dll' ;
GO
CREATE TYPE Utf8String 
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string] ;
GO

C. 建立使用者定義資料表類型

下列範例會建立有兩個資料行的使用者定義資料表類型。如需有關如何建立及使用資料表值參數的詳細資訊，請參閱＜資料表值參數 (Database Engine)＞。

/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE 
    ( LocationName VARCHAR(50)
    , CostRate INT )
GO