Share via

  • Article

CREATE TYPE (Transact-SQL)

Creates an alias data type or a user-defined type in the current database. The implementation of an alias data type is based on a SQL Server native system type. A user-defined type is implemented through a class of an assembly in the Microsoft .NET Framework common language runtime (CLR). To bind a user-defined type to its implementation, the CLR assembly that contains the implementation of the type must first be registered in SQL Server by using CREATE ASSEMBLY.

Note

The ability to run CLR code is off by default in SQL Server. You can create, modify and drop database objects that reference managed code modules, but these references will not execute in SQL Server unless the clr enabled Option is enabled by using sp_configure.

Topic link iconTransact-SQL Syntax Conventions

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 } 
}

Arguments

  • schema_name
    Is the name of the schema to which the alias data type or user-defined type belongs.

  • type_name
    Is the name of the alias data type or user-defined type. Type names must comply with the rules for identifiers.

  • base_type
    Is the SQL Server supplied data type on which the alias data type is based. base_type is sysname, with no default, and can be one of the following values:

    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 can also be any data type synonym that maps to one of these system data types.

  • precision
    For decimal or numeric, is a non-negative integer that indicates the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. For more information, see decimal and numeric (Transact-SQL).

  • scale
    For decimal or numeric, is a non-negative integer that indicates the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision. For more information, see decimal and numeric (Transact-SQL).

  • NULL | NOT NULL
    Specifies whether the type can hold a null value. If not specified, NULL is the default.

  • assembly_name
    Specifies the SQL Server assembly that references the implementation of the user-defined type in the common language runtime. assembly_name should match an existing assembly in SQL Server in the current database.

  • [.class_name ]
    Specifies the class within the assembly that implements the user-defined type. class_name must be a valid identifier and must exist as a class in the assembly with assembly visibility. class_name is case-sensitive, regardless of the database collation, and must exactly match the class name in the corresponding assembly. The class name can be a namespace-qualified name enclosed in square brackets (    [ ]    ) if the programming language that is used to write the class uses the concept of namespaces, such as C#. If class_name is not specified, SQL Server assumes it is the same as type_name. 

  • <column_definition>
    Defines the columns for a user-defined table type. For more information about columns, see Adding and Deleting Columns.

  • <data type>
    Defines the data type in a column for a user-defined table type. For more information about data types, see Data Types (Transact-SQL). For more information about tables, see CREATE TABLE (Transact-SQL).

  • <column_constraint>
    Defines the column constraints for a user-defined table type. Supported constraints include PRIMARY KEY, UNIQUE, and CHECK. For more information about constraints, see Enforcing Data Integrity. For more information about tables, see CREATE TABLE (Transact-SQL).

  • <computed_column_definition>
    Defines a computed column expression as a column in a user-defined table type. For more information about computed columns, see Computed Columns. For more information about tables, see CREATE TABLE (Transact-SQL).

  • <table_constraint>
    Defines a table constraint on a user-defined table type. Supported constraints include PRIMARY KEY, UNIQUE, and CHECK. For more information about table constraints, see Constraints.

  • <index_option>
    Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. For more information about index options, see CREATE INDEX (Transact-SQL).

Remarks

When CREATE TYPE is used to create a CLR user-defined type, the database compatibility must be 90.

The class of the assembly that is referenced in assembly_name, together with its methods, should satisfy all the requirements for implementing a user-defined type in SQL Server. For more information about these requirements, see CLR User-Defined Types.

Additional considerations include the following:

  • The class can have overloaded methods, but these methods can be called only from within managed code, not from Transact-SQL.

  • Any static members must be declared as const or readonly if assembly_name is SAFE or EXTERNAL_ACCESS.

Within a database, there can be only one user-defined type registered against any specified type that has been uploaded in SQL Server from the CLR. If a user-defined type is created on a CLR type for which a user-defined type already exists in the database, CREATE TYPE fails with an error. This restriction is required to avoid ambiguity during SQL Type resolution if a CLR type can be mapped to more than one user-defined type.

If any mutator method in the type does not return void, the CREATE TYPE statement does not execute.

To modify a user-defined type, you must drop the type by using a DROP TYPE statement and then re-create it.

Unlike user-defined types that are created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. This permission must be granted separately.

In user-defined table types, structured user-defined types that are used in column_name <data type> are part of the database schema scope in which the table type is defined. To access structured user-defined types in a different scope within the database, use two-part names.

In user-defined table types, the primary key on computed columns must be PERSISTED and NOT NULL.

Permissions

Requires CREATE TYPE permission in the current database and ALTER permission on schema_name. If schema_name is not specified, the default name resolution rules for determining the schema for the current user apply. If assembly_name is specified, a user must either own the assembly or have REFERENCES permission on it.

Examples

A. Creating an alias type based on the varchar data type

The following example creates an alias type based on the system-supplied varchar data type.

CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

B. Creating a user-defined type

The following example creates a type Utf8String that references class utf8string in the assembly utf8string. Before creating the type, assembly utf8string is registered in the local database.

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

C. Creating a user-defined table type

The following example creates a user-defined table type that has two columns. For more information about how to create and use table-valued parameters, see Table-Valued Parameters (Database Engine).

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