CREATE TYPE (Transact-SQL)

發行項
11/19/2024

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 SQL 資料庫

在 SQL Server 或 Azure SQL Database 的目前資料庫中建立別名資料類型或使用者定義型別。別名數據類型的實作是以原生系統類型資料庫引擎為基礎。使用者定義型別是使用 Microsoft .NET Framework 通用語言執行平台 (CLR) 中組件的類別來實作的。若要將使用者定義型別系結至其實作，包含型別實作的CLR元件必須先使用 CREATE ASSEMBLY 在資料庫引擎中註冊。

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

注意

本文會探討如何將 .NET Framework CLR 整合至 SQL Server。 CLR 整合不適用於 Microsoft Fabric 中的 Azure SQL 資料庫或 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 } ]
}

引數

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 (Transact-SQL)。

scale

對於十進位或數值，小數字數是非負整數，表示小數點右邊可儲存的小數位數上限，而且必須小於或等於有效位數。如需詳細資訊，請參閱 decimal 和 numeric (Transact-SQL)。

NULL | NOT NULL

指定類型可否保留 Null 值。如果未指定，則預設為 NULL。

assembly_name

適用於：SQL Server

指定在通用語言執行平台中參考使用者定義型別實作的 SQL Server 組件。 assembly_name 必須符合目前資料庫中 SQL Server 的現有組件。

注意

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 資料庫和 Azure SQL 受控執行個體。

指定要在資料表上建立索引。這可以是叢集的索引或非叢集索引。索引包含列出的數據行，並以遞增或遞減順序排序數據。

INDEX

您必須指定資料列和資料表索引做為語句的 CREATE TABLE 一部分。 CREATE INDEX 記憶體最佳化資料表不支援和 DROP INDEX 。

MEMORY_OPTIMIZED

適用於：SQL Server 2014 （12.x）和更新版本、Azure SQL 資料庫和 Azure SQL 受控執行個體。 Azure SQL 受控執行個體不支援一般用途層中的記憶體優化數據表。

指出資料表類型是否為記憶體最佳化。這個選項預設為關閉;數據表（類型）不是記憶體優化數據表（type）。記憶體最佳化的資料表類型是記憶體最佳化的使用者資料表，其結構描述保存在磁碟上，類似於其他使用者資料表。

BUCKET_COUNT

適用於：SQL Server 2014 （12.x）和更新版本、Azure SQL 資料庫、Azure SQL 資料庫和 Azure SQL 受控執行個體。

指出應該在雜湊索引中建立的貯體數目。哈希索引中的最大值 BUCKET_COUNT 為 1,073,741,824。如需貯體計數的詳細資訊，請參閱記憶體優化數據表上的索引。 bucket_count 為必要的引數。

HASH

適用於：SQL Server 2014 （12.x）和更新版本、Azure SQL 資料庫、Azure SQL 資料庫和 Azure SQL 受控執行個體。

表示 HASH 已建立索引。只有記憶體最佳化的資料表才支援雜湊索引。

備註

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

其他考量包括以下各項：

類別可以包含多載的方法，但這些方法只能從 Managed 程式代碼內呼叫，而不是從 Transact-SQL 呼叫。
如果 assembly_name 為或，則任何靜態成員都必須宣告為 const 或 readonly。SAFEEXTERNAL_ACCESS

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

如果類型中的任何 Mutator 方法未傳回 void，則 CREATE TYPE 語句不會執行。

若要修改使用者定義類型，您必須使用 DROP TYPE 語句卸除類型，然後重新建立它。

不同於使用 sp_addtype所建立的使用者定義型別，公用資料庫角色不會自動授與 REFERENCES 使用 CREATE TYPE所建立之型別的許可權。這個權限必須另外授與。

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

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

在 Fabric SQL 資料庫中，可以建立使用者定義型別，但不會鏡像到 Fabric OneLake，而且會在鏡像中略過使用者定義型別的數據行。

記憶體優化數據表類型

從 SQL Server 2014 (12.x) 開始，資料表類型的資料處理可以在主要記憶體中執行，而不是在磁碟上。如需詳細資訊，請參閱記憶體內部 OLTP 概觀和使用案例。如需如何建立經記憶體最佳化的資料表類型的範例程式碼，請參閱建立經記憶體最佳化的資料表和原生編譯的預存程序。

權限

CREATE TYPE需要目前資料庫中的許可權，以及ALTERschema_name的許可權。如果未 指定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. 建立使用者定義的數據表類型

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

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

D. 使用主鍵和索引建立使用者定義的數據表類型

以下範例會建立有三個資料行的使用者定義資料表類型，其中一個 (Name) 是主索引鍵，另一個 (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 TYPE (Transact-SQL)

Syntax

引數

schema_name

type_name

base_type

有效位數

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<資料類型>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED |NONCLUSTERED ] （ column_name [ ASC |DESC ] [ ， ...n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

備註

記憶體優化數據表類型

權限

範例

A. 根據 varchar 數據類型建立別名類型

B. 建立使用者定義型別

C. 建立使用者定義的數據表類型

D. 使用主鍵和索引建立使用者定義的數據表類型

意見反應

其他資源

共用方式為

CREATE TYPE (Transact-SQL)

Syntax

引數

schema_name

type_name

base_type

有效位數

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<資料類型>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED |NONCLUSTERED ] （ column_name [ ASC |DESC ] [ ， ...n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

備註

記憶體優化數據表類型

權限

範例

A. 根據 varchar 數據類型建立別名類型

B. 建立使用者定義型別

C. 建立使用者定義的數據表類型

D. 使用主鍵和索引建立使用者定義的數據表類型

相關內容

意見反應

其他資源