通过

CREATE TYPE (Transact-SQL)

  • 项目

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

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

适用于：SQL Server（SQL Server 2008 至当前版本），Azure SQL Database（初始版本至当前版本）。

主题链接图标 Transact-SQL 语法约定

语法

Disk-Based 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> }
        [ <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 }
}

Memory-Optimized Table Type Syntax
CREATE TYPE [schema_name. ] type_name
AS TABLE ( { <column_definition> }
    |  [ <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}]
}

参数

  • 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。

  • assembly_name

    适用于：SQL Server 2008 到 SQL Server 2014。

    指定可在公共语言运行库中引用用户定义类型的实现的 SQL Server 程序集。assembly_name 应与当前数据库的 SQL Server 中的现有程序集匹配。

    备注

    EXTERNAL_NAME 在包含数据库中不可用。

  • [. class_name ]

    适用于：SQL Server 2008 到 SQL Server 2014。

    指定实现用户定义类型的程序集内的类。 class_name 必须是有效的标识符，而且必须在具有程序集可见性的程序集中作为类存在。 class_name 区分大小写，也不管数据库的排序规则如何，且必须与对应的程序集中的类名完全匹配。 如果用于编写类的编程语言使用命名空间概念（例如 C#），则类名可以是用方括号 ([ ]) 括起的限定命名空间的名称。 如果未指定 class_name，SQL Server 假定该名称与 type_name 相同。

  • <column_definition>
    定义用户定义表类型的列。

  • <数据类型>
    定义用户定义表类型的列中的数据类型。 有关数据类型的详细信息，请参阅数据类型 (Transact-SQL)。 有关表的详细信息，请参阅 CREATE TABLE (SQL Server)。

  • <column_constraint>
    定义用户定义表类型的列约束。 支持的约束包括 PRIMARY KEY、UNIQUE 和 CHECK。 有关表的详细信息，请参阅 CREATE TABLE (SQL Server)。

  • <computed_column_definition>
    将计算列表达式定义为用户定义表类型中的列。 有关表的详细信息，请参阅 CREATE TABLE (SQL Server)。

  • <table_constraint>
    定义用户定义表类型的表约束。 支持的约束包括 PRIMARY KEY、UNIQUE 和 CHECK。

  • <index_option>
    指定对唯一聚集索引或唯一非聚集索引执行多行插入操作时出现重复键值的错误响应。 有关索引选项的详细信息，请参阅 CREATE INDEX (Transact-SQL)。

  • INDEX
    必须在 CREATE TABLE 语句中指定列索引和表索引。 内存优化表不支持 CREATE INDEX 和 DROP INDEX。

  • MEMORY_OPTIMIZED

    适用于：SQL Server 2014 到 SQL Server 2014。

    指示表类型是否为内存优化表。 默认情况下，此选项处于关闭状态；表（类型）不是内存优化表（类型）。 内存优化表类型是内存优化用户表，它保留在磁盘上的架构与其他用户表类似。 内存优化表类型只能从本机编译存储过程访问。

  • BUCKET_COUNT

    适用于：SQL Server 2014 到 SQL Server 2014。

    指示应在哈希索引中创建的存储桶数。 哈希索引中 BUCKET_COUNT 的最大值为 1,073,741,824。 有关 Bucket 计数的详细信息，请参阅决定哈希索引的正确存储桶数。 bucket_count 是必需的参数。

  • HASH

    适用于：SQL Server 2014 到 SQL Server 2014。

    指示创建哈希索引。 仅在内存优化表中支持哈希索引。

注释

在 assembly_name 中引用的程序集的类及其方法应满足在 SQL Server 中实现用户定义类型的所有要求。 有关这些要求的详细信息，请参阅 CLR 用户定义类型。

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

  • 类可以具有重载方法，但只能从托管代码内而不能从 Transact-SQL 调用这些方法。

  • 如果 assembly_name 是 SAFE 或 EXTERNAL_ACCESS，则必须将所有静态成员声明为 const 或 readonly。

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

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

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

与使用 sp_addtype 创建的用户定义类型不同，对于使用 CREATE TYPE 创建的类型，不会向 public 数据库角色自动授予该类型的 REFERENCES 权限。 此权限必须单独授予。

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

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

内存优化表类型

从 SQL Server 2014 开始，可在主内存中而不是磁盘上执行表类型中的数据的处理。 有关详细信息，请参阅内存中 OLTP（内存中优化）。 有关说明如何创建内存优化表类型的代码示例，请参阅创建内存优化表和本机编译的存储过程。 有关内存优化表类型的详细信息，请参阅内存优化表变量。

权限

要求在当前数据库中具有 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。

适用于：SQL Server 2008 到 SQL Server 2014。

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

C.创建用户定义表类型

下面的示例创建一个具有两列的用户定义表类型。 有关如何创建和使用表值参数的详细信息，请参阅使用表值参数（数据库引擎）。

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

请参阅

参考

CREATE ASSEMBLY (Transact-SQL)

DROP TYPE (Transact-SQL)

EVENTDATA (Transact-SQL)