CREATE TYPE (Transact-SQL)

项目
08/22/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

在 SQL Server 或 Azure SQL 数据库中的当前数据库中创建别名数据类型或用户定义类型。别名数据类型的实现基于数据库引擎本机系统类型。用户定义类型通过 Microsoft.NET Framework 公共语言运行时 (CLR) 中的程序集的类来实现。若要将用户定义的类型绑定到其实现，必须先使用 CREATE ASSEMBLY 在数据库引擎中注册包含该类型的实现的 CLR 程序集。

默认情况下，在 SQL Server 中运行 CLR 代码的功能处于关闭状态。可以创建、修改和删除引用托管代码模块的数据库对象。但是，除非使用 sp_configure 启用 clr 选项，否则这些引用不会在 SQL Server 中执行。

注意

本主题讨论 .NET Framework CLR 与 SQL Server 的集成。 CLR 集成不适用于Azure SQL 数据库。

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 ] )
     [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 还可以是映射到这些系统数据类型之一的任何数据类型同义词。

精度

对于 十进制 或数字，精度是一个非负整数，指示可存储的小数位数的最大总数，同时位于小数点的左侧和右侧。有关详细信息，请参阅 decimal 和 numeric (Transact-SQL)。

scale

对于 十进制 或数字，刻度是一个非负整数，指示可存储在小数点右侧的十进制数字的最大数目，并且必须小于或等于精度。有关详细信息，请参阅 decimal 和 numeric (Transact-SQL)。

NULL | NOT 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 托管实例不支持“常规用途”层中的内存优化表。

指示表类型是否为内存优化表。此选项默认处于关闭状态;表（类型）不是内存优化表（类型）。内存优化表类型是内存优化用户表，它保留在磁盘上的架构与其他用户表类似。

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 用户定义类型。

其他注意事项包括以下几点：

该类可以包含重载的方法，但这些方法只能从托管代码内部调用，而不能从 Transact-SQL 调用。
如果assembly_name为或，则必须将任何静态成员声明为常数或只读。EXTERNAL_ACCESSSAFE

在数据库内，任何从 CLR 上载到 SQL Server 中的指定类型都只能注册一个用户定义类型。如果在数据库中已存在用户定义类型的 CLR 类型上创建用户定义的类型， CREATE TYPE 则失败并出现错误。如果一个 CLR 类型可被映射到多个用户定义类型，则要求使用此限制来避免 SQL 类型解析过程中的混乱情况。

如果类型中的任何 mutator 方法不返回 void，则 CREATE TYPE 语句不会执行。

若要修改用户定义的类型，必须使用语句删除该类型 DROP TYPE ，然后重新创建它。

与使用sp_addtype的用户定义类型不同，公共数据库角色不会自动授予REFERENCES对使用 <a0/a0> 创建的类型的权限。此权限必须单独授予。

在用户定义表类型中，column_name<data type> 中使用的结构化用户定义类型是定义表类型的数据库架构作用域的一部分。若要访问数据库不同作用域中的结构化用户定义类型，请使用由两部分组成的名称。

在用户定义的表类型中，计算列的主键必须是 PERSISTED 和 NOT NULL。

内存优化表类型

从 SQL Server 2014 (12.x) 开始，可在主内存中而不是磁盘上执行表类型中的数据的处理。有关详细信息，请参阅内存中 OLTP 概述和使用方案。对于演示如何创建内存优化表类型的代码示例，请参阅创建内存优化表和本机编译的存储过程。

权限

CREATE TYPE需要当前数据库中的权限和ALTER对schema_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)

语法

参数

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)

语法

参数

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. 使用主键和索引创建用户定义的表类型

相关内容

反馈

其他资源