User Defined Table Types SQL Server 2008–SQL Server 2016

Examples

• 2

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

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

  created Aug 8 '16 at 7:17

  
  discuss
• 2

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

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

  created Aug 8 '16 at 7:09

  
  discuss
• 2

  Creating a UDT with a unique constraint:
  2

  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.

  created Aug 8 '16 at 7:14

  
  discuss
• 2

  Creating a UDT with multiple columns
  2

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

  created Aug 8 '16 at 7:10

  
  discuss