CREATE TYPE (Transact-SQL)

  • Article
  • ten minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

Creates an alias data type or a user-defined type in the current database in SQL Server or Azure SQL Database. The implementation of an alias data type is based on a SQL Server native system type. A user-defined blazon is implemented through a class of an associates in the Microsoft.Cyberspace 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 exist registered in SQL Server by using CREATE Assembly.

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

Note

The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR integration does not use to Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- User-defined Data Type syntax     CREATE Type [ schema_name. ] type_name   {        [       FROM base_type          [ ( precision [ , calibration ] ) ]         [ NULL | Not Naught ]     ]     | EXTERNAL NAME assembly_name [ .class_name ]        | Every bit Tabular array ( { <column_definition> | <computed_column_definition> [ ,... n ] }       [ <table_constraint> ] [ ,... north ]           [ <table_index> ] [ ,... n ] } )   } [ ; ]      <column_definition> ::=   column_name <data_type>       [ COLLATE collation_name ]        [ Nil | Not NULL ]       [            DEFAULT constant_expression ]          | [ IDENTITY [ ( seed ,increment ) ]        ]       [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]       <data type> ::=    [ type_schema_name . ] type_name        [ ( precision [ , scale ] | max |                    [ { CONTENT | Certificate } ] 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 [ Non Zero ] ]   [        { PRIMARY Cardinal | UNIQUE }           [ Amassed | NONCLUSTERED ]           [                WITH ( <index_option> [ ,...n ] )           ]       | CHECK ( logical_expression )    ]       <table_constraint> ::=   {        { Principal Central | UNIQUE }            [ CLUSTERED | NONCLUSTERED ]        ( column [ ASC | DESC ] [ ,...n ] )            [        WITH ( <index_option> [ ,...n ] )            ]       | CHECK ( logical_expression )    }       <index_option> ::=   {       IGNORE_DUP_KEY = { ON | OFF }   }    < table_index > ::=     Alphabetize constraint_name        [ Amassed | NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )} }                          
              -- User-defined Memory Optimized Tabular array Types syntax   CREATE TYPE [schema_name. ] type_name   Every bit Tabular array ( { <column_definition> [ ,... northward ] }       | [ <table_constraint> ] [ ,... north ]         | [ <table_index> ] [ ,... n ] } )     [ WITH ( <table_option> [ ,... due north ] ) ]    [ ; ]      <column_definition> ::=   column_name <data_type>       [ COLLATE collation_name ] [ Null | Non NULL ]    [         [ IDENTITY [ (1 , one) ]       ]       [ <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 > ::=   { Main KEY { NONCLUSTERED HASH (column [ ,... n ] )                     WITH ( BUCKET_COUNT = bucket_count )                 | NONCLUSTERED ( column [ ASC | DESC ] [ ,... northward ] )  	       }  }      <column_index> ::=     INDEX index_name   { [ NONCLUSTERED ] HASH WITH ( BUCKET_COUNT = bucket_count )       | NONCLUSTERED  }      < table_index > ::=     Alphabetize constraint_name   { [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)  	|  [ NONCLUSTERED ]  ( cavalcade [ ASC | DESC ] [ ,... n ] )  }      <table_option> ::=   {       [ MEMORY_OPTIMIZED = { ON | OFF } ]   }

Arguments

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

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

base_type
Is the SQL Server supplied information type on which the allonym data type is based. base_type is sysname, with no default, and can exist i of the following values:

bigint
binary( n )
bit
char( n )

engagement
datetime
datetime2
datetimeoffset

money
nchar( northward )
ntext
numeric

nvarchar( n | max)
real
smalldatetime
smallint

smallmoney
sql_variant
text
time

tinyint
uniqueidentifier
varbinary( due north | max)
varchar( n | max)

base_type tin can also be any data type synonym that maps to 1 of these organization 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 signal. For more than information, encounter decimal and numeric (Transact-SQL).

scale
For decimal or numeric, is a not-negative integer that indicates the maximum number of decimal digits that tin 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).

Zilch | Non NULL
Specifies whether the type can hold a null value. If non specified, Cypher is the default.

assembly_name
Applies to: SQL Server (Starting with SQL Server 2008).

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

Note

EXTERNAL_NAME is non bachelor in a independent database.

[. class_name ]
Applies to: SQL Server (Starting with SQL Server 2008).

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

<column_definition>
Defines the columns for a user-divers tabular array type.

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

<column_constraint>
Defines the column constraints for a user-defined table blazon. Supported constraints include Principal Cardinal, UNIQUE, and CHECK. For more than information most tables, see CREATE TABLE (Transact-SQL).

<computed_column_definition>
Defines a computed column expression equally a cavalcade in a user-defined table blazon. For more information about tables, come across CREATE Tabular array (Transact-SQL).

<table_constraint>
Defines a table constraint on a user-divers table type. Supported constraints include PRIMARY KEY, UNIQUE, and CHECK.

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

Index *index_name* [ CLUSTERED | NONCLUSTERED ] (*column_name* [ ASC | DESC ] [ ,... *n* ] )

Applies to: SQL Server (Starting with SQL Server 2014 (12.x)), Azure SQL Database, and Azure SQL Managed Example.

Specifies to create an index on the table. This tin be a clustered index, or a nonclustered index. The index volition comprise the columns listed, and volition sort the data in either ascending or descending society.

INDEX
Yous must specify column and tabular array indexes as part of the CREATE TABLE argument. CREATE INDEX and DROP Index are not supported for memory-optimized tables.

MEMORY_OPTIMIZED
Applies to: SQL Server (Starting with SQL Server 2014 (12.ten)), Azure SQL Database, and Azure SQL Managed Instance. Azure SQL Managed Instance does not support retentivity optimized tables in Full general Purpose tier.

Indicates whether the table type is retention optimized. This pick is off by default; the table (blazon) is not a retentiveness optimized table (type). Memory optimized table types are memory-optimized user tables, the schema of which is persisted on disk similar to other user tables.

BUCKET_COUNT
Applies to: SQL Server (Starting with SQL Server 2014 (12.x)) and Azure SQL Database, Azure SQL Database, and Azure SQL Managed Instance.

Indicates the number of buckets that should be created in the hash index. The maximum value for BUCKET_COUNT in hash indexes is 1,073,741,824. For more data about bucket counts, come across Indexes for Memory-Optimized Tables. bucket_count is a required argument.

HASH
Applies to: SQL Server (Starting with SQL Server 2014 (12.x)) and Azure SQL Database, Azure SQL Database, and Azure SQL Managed Case.

Indicates that a HASH index is created. Hash indexes are supported just on memory optimized tables.

Remarks

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 than information nearly these requirements, see CLR User-Defined Types.

Boosted considerations include the following:

  • The grade can have overloaded methods, but these methods can be called simply from within managed lawmaking, non from Transact-SQL.

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

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

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

To modify a user-defined type, you must drop the type by using a Drib TYPE statement and then copy information technology.

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-divers tabular array types, structured user-defined types that are used in column_name <data type> are part of the database schema telescopic in which the table type is defined. To admission structured user-divers types in a different scope within the database, use ii-part names.

In user-divers tabular array types, the principal primal on computed columns must exist PERSISTED and NOT NULL.

Retentiveness-Optimized Tabular array Types

Offset in SQL Server 2014 (12.10), processing data in a table blazon tin can be washed in main memory, and not on deejay. For more information, see In-Retentivity OLTP (In-Retention Optimization). For lawmaking samples showing how to create retention-optimized table types, meet Creating a Retention-Optimized Table and a Natively Compiled Stored Procedure.

Permissions

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

If whatever columns in the CREATE Table statement are defined to be of a user-defined type, REFERENCES permission on the user-defined type is required.

Notation

A user creating a table with a column that uses a user-divers type needs the REFERENCES permission on the user-defined type. If this tabular array must exist created in TempDB, then either the REFERENCES permission needs to be granted explicitly each time before the table is created, or this information type and REFERENCES permissions need to be added to the Model database. If this is done, then this data blazon and permissions will exist available in TempDB permanently. Otherwise, the user-divers data blazon and permissions will disappear when SQL Server is restarted. For more information, see CREATE TABLE

Examples

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

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

              CREATE TYPE SSN   FROM varchar(11) Non NULL ;

B. Creating a user-defined type

The following case creates a type Utf8String that references class utf8string in the assembly utf8string. Before creating the type, associates utf8string is registered in the local database. Replace the binary portion of the CREATE ASSEMBLY statement with a valid description.

Applies to: SQL Server (Starting with SQL Server 2008).

              CREATE Associates utf8string   AUTHORIZATION [dbi]    FROM 0x4D... ;   GO   CREATE Type Utf8String    EXTERNAL Proper noun utf8string.[Microsoft.Samples.SqlServer.utf8string] ;   Become

C. Creating a user-defined table blazon

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

              CREATE Type LocationTableType Every bit TABLE        ( LocationName VARCHAR(50)       , CostRate INT );   Go

D. Creating a user-defined table type with primary key and index

The following case creates a user-defined tabular array type that has 3 columns, ane of which (Name) is the primary key and some other (Price) has a nonclustered index. For more data about how to create and use table-valued parameters, see Use Table-Valued Parameters (Database Engine).

              CREATE Type InventoryItem Equally TABLE ( 	[Proper noun] NVARCHAR(50) NOT Aught, 	SupplierId BIGINT Not Zippo, 	Price DECIMAL (18, 4) NULL, 	PRIMARY KEY ( 		Name 	), 	INDEX IX_InventoryItem_Price ( 		Cost 	) ) Become

Run across Also

CREATE ASSEMBLY (Transact-SQL)
Drib Type (Transact-SQL)
EVENTDATA (Transact-SQL)
CLR User-Defined Types
Working with User-Defined Types in SQL Server