Create user-defined Table Type

Creating a custom table type using the Create type, Syntax is similar to the syntax for CREATE table.

CREATETYPE[schema_name.]type_name{ fromBase_type[(precision [, scale] ) ]    [NULL | Not NULL]   |  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] ] <Column_constraint>::= {     { PRIMARY KEY | UNIQUE }         [CLUSTERED | Nonclustered]         [With (<index_option> [,... n] ) ]  | CHECK(logical_expression)}<Table_constraint>::={     { PRIMARY KEY | UNIQUE }         [CLUSTERED | Nonclustered](column_name[ASC | DESC] [,... N] )         [With (<index_option> [,... n] )]    | CHECK(logical_expression)}

Examples

1,creating an alias type based on the varchar data type

CREATE TYPE dbo. Phonenumfromvarchar(one notNULL ;

2,creating a user-defined table type

/*Create a user-defined table type*/CREATETYPE dbo. Locationtabletype as TABLE(LocationIDbigint  not NULL Primary Key Clustered, LocationNameVARCHAR( -) not NULL, Costratefloat Check(costratebetween 0.0  and 1.0) )GO

3,drop user-defined Type

drop type dbo. Locationtabletype

Remarks

The DROP TYPE statement would not execute if any of the following is true:

• there is tables in the database that contain columns of the alias data type O R the user-defined type. Information about alias or user-defined type columns can is obtained by querying the sys.columns or sys.column_type_usages Catalog views.

• there is computed columns, CHECK constraints, schema-bound views, and Schema-bound functions whose definitions reference the alias or user-defined type. Information about these references can is obtained by querying the Sys.sql_expression_dependencies catalog view.

• there is functions, stored procedures, or triggers created in the Databa SE, and these routines use variables and parameters of the alias or user-defined type. Information about alias or user-defined type parameters can is obtained by querying the sys.parameters or sys.parameter_ty Pe_usages catalog views.

Reference doc:

CREATE TYPE (Transact-SQL)

DROP TYPE (Transact-SQL)

Create user-defined Table Type