Tutorial by Examples | RIP Tutorial

creating a UDT with a single int column that is also a primary key

CREATE TYPE dbo.Ids as TABLE ( Id int PRIMARY KEY )

Microsoft SQL Server • User Defined Table Types

Creating a UDT with multiple columns

CREATE TYPE MyComplexType as TABLE ( Id int, Name varchar(10) )

Microsoft SQL Server • User Defined Table Types

Creating a UDT with a unique constraint:

CREATE TYPE MyUniqueNamesType as TABLE ( FirstName varchar(10), LastName varchar(10), UNIQUE (FirstName,LastName) ) Note: constraints in user defined table types can not be named.

Microsoft SQL Server • User Defined Table Types

Creating a UDT with a primary key and a column with a default value:

CREATE TYPE MyUniqueNamesType as TABLE ( FirstName varchar(10), LastName varchar(10), CreateDate datetime default GETDATE() PRIMARY KEY (FirstName,LastName) )

Microsoft SQL Server • User Defined Table Types