通过

  • 项目

CREATE TYPE (Transact-SQL)

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

注意注意

默认情况下，在 SQL Server 中运行 CLR 代码的功能处于关闭状态。您可以创建、修改和删除引用托管代码模块的数据库对象，但除非使用 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。

  • assembly_name
    指定可在公共语言运行库中引用用户定义类型的实现的 SQL Server 程序集。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 用户定义类型。

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

  • 类可以具有重载方法，但只能从托管代码内而不能从 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。

权限

要求在当前数据库中具有 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. 创建用户定义表类型

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

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